SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Location
    Colorado
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pull usernames from Users table based on IDs in Issues table

    Hello,

    I am trying to work out a solution I found by a good old google search but am getting nothing but unhelpful syntax errors.

    Here is the link to the short answer that worked for the other person: SQL Join, 2 tables, same fields

    In case you don't want to read through that, here is the basic idea. Two tables. Table 1 has messages with IDs stored for the MsgFrom and MsgTo. Table 2 is those users (ID, username). It looks like you have to do two joins. This is how the solution reads.

    Code:
    select m.*, u1.userName as Sender, u2.userName as Recipient 
    from tabMessages as m 
      inner join tabUsers as u1 
        on u1.userId=m.msgFrom 
      inner join tabUsers as u2 
        on u2.userId=m.msgTo 
    where m.msgId = @someParameter;
    My problem seems similar enough that this should get me what I need. Here is what I have specifically (trimmed down for simplicity).

    Issues table:
    issueid description addedby assignedto
    1 heat 1 2
    2 water 1 3
    3 roof 2 1

    Users table:
    userid username
    1 Adam
    2 Brian
    3 Chad

    What I am looking for is a query that will output the following:
    issueid description addedby assignedto
    1 heat Adam Brian
    2 water Adam Chad
    3 roof Brian Adam

    I think part of the problem may be that I am working in Microsoft Access. Here is the query I tried based on the solution above. I just get the same syntax error each time.

    Code:
    SELECT m.*, f.username AS AddedBy, t.username AS AssignedTo FROM issues AS m
        INNER JOIN users AS f on m.addedby = f.userid
        INNER JOIN users AS t on m.assignedto = t.userid;
    Any idea what I am doing wrong? Thank you in advance for your help.

    Swani

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by swani View Post
    I think part of the problem may be that I am working in Microsoft Access.
    yup

    multiple joins need to be nested in ms access

    look for the red parentheses here --
    Code:
    SELECT m.*
         , f.username AS AddedBy
         , t.username AS AssignedTo 
      FROM (
            issues AS m
    INNER 
      JOIN users AS f 
        ON m.addedby = f.userid
           )
    INNER 
      JOIN users AS t 
        ON m.assignedto = t.userid;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Location
    Colorado
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you SO much. I feel like I am really close but missing something. This query gives me the result I am looking for with one problem. The m.AssignedTo column and the m.AddedBy (I believe from the m.* part of the query) columns just have the IDs. Normal, I think, given that the m.* should pull all fields. The AddedBy and AssignedTo (I believe from the columns created by the "AS" parts of the query) are blank. If I go into Design Mode and add the f.username and t.username from the two tables, the names I want show up. But, the column names where the usernames show up are labeled f.username and t.username. I'm sure I could make this work but, as I said, I feel like I am so close to doing this properly I just wanted to push my luck a bit more and go for the fully elegant solution that just returns the table I am hoping for. Here is the SQL the comes from adding the additional fields in Design Mode:

    Code:
    SELECT m.*, 
                f.username AS AddedBy, 
                t.username AS AssignedTo, 
                f.username, 
                t.username
    FROM (issues AS m INNER JOIN users AS f ON m.addedby = f.userid) 
    INNER JOIN users AS t ON m.assignedto = t.userid;
    I am sure this isn't right but it's the only way I can make the actual usernames from the USERS table appear. What am I doing wrong?

    Thanks in advance to any who can help me finish this off.

    And mucho thanks to r937 for getting me this far.

    Swani

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try aliasing the usernames to column names that don't actually reproduce the name of an existing column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Location
    Colorado
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try aliasing the usernames to column names that don't actually reproduce the name of an existing column
    Duh. That was it. Thank you again for your help on this. Much appreciated.

    Swani


Tags for this Thread

Bookmarks

Posting Permissions

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