Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2015
    Posts
    3

    Answered: How to accomplish lookups/joins

    My company is using an out-of-the-box type intranet product, so we cannot control the design of the affiliated SQL tables. With that said, the intranet product has a Chat function that we need to be able to retain the history for. I'm pretty new to SQL and am unsure how to accomplish writing a query to show the history in a way that will be easily legible. Here are the table structures:

    ChatHistory Table:
    • MessageID
    • UserID (Sender)
    • ChatID (Receiver)


    ChatMessage Table:
    • MessageID
    • Message (actual chat message)


    UserProfile Table:
    • UserID
    • UserName


    The difficulty I'm having is that the ChatHistory table references two user IDs (the sender and the receiver), but both correlate to the same UserID in the UserProfile table. As far as I'm aware (and please correct me if I'm wrong) I can only join tables together one time. I use MS Access on a regular basis, which allows the use of the DLookup function. That's basically what I want to accomplish here. I've seen that SQL does have a Lookup function, but either I wrote the syntax incorrectly or else it also wasn't working for what I wanted to accomplish. This seems like it should be simple. I just want the query to basically show the structure of the ChatHistory table, but just with the information in it rather than the IDs, as such:

    Sender: John Doe
    Receiver: Jane Doe
    Message: Good afternoon!

    Can anyone tell me how I might accomplish this? Here is the code as I have it written so far:

    SELECT ChatHistory.MessageID
    ,UserProfile.UserName
    ,ChatHistory.UserID
    ,ChatHistory.ChatID
    ,ChatMessages.Message
    FROM [dbo].[ChatHistory]
    INNER JOIN [dbo].ChatMessages on ChatHistory.MessageID=ChatMessages.MessageID AND ChatHistory.UserID = ChatMessages.UserID
    JOIN [dbo].UserProfile on ChatHistory.UserID=UserProfile.UserID
    ORDER BY ChatHistory.MessageID

  2. Best Answer
    Posted by healdem

    "is standard SQL, its available in all SQL engines Ive seen so far including Access,
    what actually are you trying to do. Im guessing its retrieve the names of the of the users (the sender and receiver), why you doint' call them that in table ChatHistoiry beats me, but each to their own

    its going to be somethign like:-
    Code:
    SELECT my, column, list, Sender.UserName, Recipient.UserName from ChatMessage as CM
    left join ChatHistory as CH on CM.MessageID = CH.MessageID
    left Join UserProfile as Sender on CH.UserID = Sender.UserID
    left Join UserProfile as Recipient on CH.UserID = Recipient.ChatID
    natch yu will need to replace my, column, list with watever (other) columns you need, use the Alias or tbale name if there are possible duplicate clumn names. its actually good practice to fully qualify the table name (using the alias) if you are JOLINING a table. you dont have to use an alias, you can use the tablename IF the tabel is used once in a join. but using a tablename where there are multipole tables can make the query longwonded and difficult to read/comprehend

    if you do use aliases make them as short as possible. if it were my query I'd probably use S instead ofSender, R instead of recipient, but I'd probably alias the column names

    Code:
    SELECT my, column, list, S.UserName as Sender, R.UserName as Recipient from ChatMessage as CM...
    "


  3. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you can join to a single multiple times. Once joining column_x to table2.column_y and once joining column_x to table2.column_z. No issues there whatsoever.
    Dave

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is standard SQL, its available in all SQL engines Ive seen so far including Access,
    what actually are you trying to do. Im guessing its retrieve the names of the of the users (the sender and receiver), why you doint' call them that in table ChatHistoiry beats me, but each to their own

    its going to be somethign like:-
    Code:
    SELECT my, column, list, Sender.UserName, Recipient.UserName from ChatMessage as CM
    left join ChatHistory as CH on CM.MessageID = CH.MessageID
    left Join UserProfile as Sender on CH.UserID = Sender.UserID
    left Join UserProfile as Recipient on CH.UserID = Recipient.ChatID
    natch yu will need to replace my, column, list with watever (other) columns you need, use the Alias or tbale name if there are possible duplicate clumn names. its actually good practice to fully qualify the table name (using the alias) if you are JOLINING a table. you dont have to use an alias, you can use the tablename IF the tabel is used once in a join. but using a tablename where there are multipole tables can make the query longwonded and difficult to read/comprehend

    if you do use aliases make them as short as possible. if it were my query I'd probably use S instead ofSender, R instead of recipient, but I'd probably alias the column names

    Code:
    SELECT my, column, list, S.UserName as Sender, R.UserName as Recipient from ChatMessage as CM...
    I'd rather be riding on the Tiger 800 or the Norton

  5. #4
    Join Date
    Mar 2015
    Posts
    3

    Resolution

    Thanks for your help, everyone. I delved further into nested SELECT statements and I ended up coming up with this, which does exactly what I want. I also added some other things I stumbled on as I was researching, such as the CONVERT function to join the first and last name of the user so that it reads nicely in the same cell. I'm a newbie and I learned a lot from this, even though it's a really simple query.

    SELECT Q1.Sent,Q1.Message,Q1.Sender,Q2.Recipient
    FROM
    (SELECT ChatMessages.Message,ChatHistory.UserID,ChatHistor y.ChatID,ChatMessages.MessageID,ChatMessages.Sent
    ,CONVERT(varchar(250),[Person].[FirstName]) + ' ' +Convert(varchar(250),[Person].[LastName]) AS Sender
    FROM ChatHistory
    JOIN ChatMessages on ChatMessages.UserID=ChatHistory.UserID
    AND ChatMessages.MessageID=ChatHistory.MessageID
    JOIN UserProfile on UserProfile.UserID = ChatHistory.UserID
    JOIN Person on Person.PersonID = UserProfile.PersonID)Q1
    JOIN
    (SELECT ChatMessages.Message,ChatHistory.UserID,ChatHistor y.ChatID,ChatMessages.MessageID
    ,CONVERT(varchar(250),[Person].[FirstName]) + ' ' +Convert(varchar(250),[Person].[LastName]) AS Recipient
    FROM ChatHistory
    JOIN ChatMessages on ChatMessages.UserID=ChatHistory.UserID
    AND ChatMessages.MessageID=ChatHistory.MessageID
    JOIN UserProfile on UserProfile.UserID = ChatHistory.ChatID
    JOIN Person on Person.PersonID = UserProfile.PersonID)Q2
    ON Q1.MessageID = Q2.MessageID
    ORDER BY Q1.MessageID

  6. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    that looks like a ton of overkill to access the userprofile table twice. Take another look at what Healdem gave you above.
    Dave

  7. #6
    Join Date
    Mar 2015
    Posts
    3

    Revised Answer

    Dave -

    Thanks for the response. Will speed be an issue if you joine the UserProfile twice?

    I did actually re-test the suggestion haldeam provided. I'm not sure why, but when I originally tried it, I couldn't get it to work. I'm going to assume I had a asyntax error somewhere that I couldn't figure out being so new to SQL. However, I was able to get this to work today and I am going to update the answer to haldeam's suggestion. This is the final code I ended up with (again, there was one other addition I inserted after my original question when I decided to use a 4th table to supply the user's full name rather than a cryptic username):

    SELECT CM.Sent
    ,CM.Message
    ,CONVERT(varchar(250),PSender.[FirstName]) + ' ' +Convert(varchar(250),PSender.[LastName]) AS Sender
    ,CONVERT(varchar(250),PRecipient.[FirstName]) + ' ' +Convert(varchar(250),PRecipient.[LastName]) AS Recipient
    FROM ChatMessages as CM
    LEFT JOIN ChatHistory as CH on CM.UserID=CH.UserID
    AND CM.MessageID=CH.MessageID
    LEFT JOIN UserProfile as USender on USender.UserID = CH.UserID
    LEFT JOIN UserProfile as URecipient on URecipient.UserID = CH.ChatID
    LEFT JOIN Person as PSender on USender.PersonID = PSender.PersonID
    LEFT JOIN Person as PRecipient on URecipient.PersonID = PRecipient.PersonID

  8. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    That looks much cleaner and easier for someone to maintain in the future. Not so much with joining to a table twice. The issue was in your original solution you were doing the whole SQL statement twice once to get the one user and again to get the other.
    Dave

  9. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by csh2013 View Post
    Dave -

    Thanks for the response. Will speed be an issue if you joine the UserProfile twice?
    ...not really, asuming the columns you are joining on are indexed
    there is no hard and fast limit ont he number of times you can join a table, heck you can use self join (join to the same table) to extract a hierarchy. ferinstance:-
    http://sqllessons.com/categories.html

    the only reason you'd have a performance issue is if the join wasn't optimised (no indexes, forcing a tablescan on a big table willo be a performance pig, and joining twice would be double a performance pig, joining 2 times, thrice a.......
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •