Pull usernames from Users table based on IDs in Issues table

Databases
1

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.


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:
[table=“width: 500, class: grid, align: left”]
[tr]
[td]issueid[/td]
[td]description[/td]
[td]addedby[/td]
[td]assignedto[/td]
[/tr]
[tr]
[td]1[/td]
[td]heat[/td]
[td]1[/td]
[td]2[/td]
[/tr]
[tr]
[td]2[/td]
[td]water[/td]
[td]1[/td]
[td]3[/td]
[/tr]
[tr]
[td]3[/td]
[td]roof[/td]
[td]2[/td]
[td]1[/td]
[/tr]
[/table]

Users table:
[table=“width: 500, class: grid, align: left”]
[tr]
[td]userid[/td]
[td]username[/td]
[/tr]
[tr]
[td]1[/td]
[td]Adam[/td]
[/tr]
[tr]
[td]2[/td]
[td]Brian[/td]
[/tr]
[tr]
[td]3[/td]
[td]Chad[/td]
[/tr]
[/table]

What I am looking for is a query that will output the following:
[table=“width: 500, class: grid, align: left”]
[tr]
[td]issueid[/td]
[td]description[/td]
[td]addedby[/td]
[td]assignedto[/td]
[/tr]
[tr]
[td]1[/td]
[td]heat[/td]
[td]Adam[/td]
[td]Brian[/td]
[/tr]
[tr]
[td]2[/td]
[td]water[/td]
[td]Adam[/td]
[td]Chad[/td]
[/tr]
[tr]
[td]3[/td]
[td]roof[/td]
[td]Brian[/td]
[td]Adam[/td]
[/tr]
[/table]

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.

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

yup :slight_smile:

multiple joins need to be nested in ms access

look for the red parentheses here –

SELECT m.*
     , f.username AS AddedBy
     , t.username AS AssignedTo 
  FROM [COLOR="#FF0000"]([/COLOR]
        issues AS m
INNER 
  JOIN users AS f 
    ON m.addedby = f.userid
       [COLOR="#FF0000"])[/COLOR]
INNER 
  JOIN users AS t 
    ON m.assignedto = t.userid;

:slight_smile:

3

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:


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

try aliasing the usernames to column names that don’t actually reproduce the name of an existing column

5

Duh. That was it. Thank you again for your help on this. Much appreciated.

Swani