Results 1 to 5 of 5
Oct 16, 2013, 17:25 #1
- Join Date
- Apr 2006
- 0 Post(s)
- 0 Thread(s)
Pull usernames from Users table based on IDs in Issues table
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;
issueid description addedby assignedto 1 heat 1 2 2 water 1 3 3 roof 2 1
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.
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;