SQL Query to show 3 user names from userID's in one Bug record.

Question

I would like to show all the usernames who have interacted with a bug in MS Access. I do not understand how to reference the same Users.UserName field 3 times in my select statement?

Here are my tables

--------------------------------------------
Table 'Bugs'
--------------------------------------------
BugID    OpendBy    ClosedBy    LastChangedBy
1           1          3               2    
2           3          4               4           
3           2          4               4
4           3          1               3

--------------------------------------------
Table 'Users'
--------------------------------------------
ID    UserName        
1     Joe      
2     Chris   
3     Steve     
4     Sarah     

I would like the query to return

BugID OpendBy ClosedBy LastChangedBy 
1       Joe     Steve     Chris
2       Steve   Sarah     Sarah
3       Chris   Sarah     Sarah
4       Steve   Joe       Steve

Answer 1

I used a LEFT OUTER JOIN below for ClosedBy, as I assume this value can be NULL. If anyof the others can be NULL, you'll need to change those joins to LEFT OUTER as well.

select b.BugID, uo.UserName as OpendBy, 
    uc.UserName as ClosedBy, 
    ul.LastName as LastChangedBy
from Bugs b
inner join Users uo on b.OpendBy = uo.ID
left outer join Users uc on b.ClosedBy = uc.ID
inner join Users ul on b.LastChangedBy = ul.ID

Answer 2

If you want to avoid all the ugly joining, you could structure this instead as a separate table with BugID, ActionType (OpenedBy, ClosedBy, LastChangedBy) and UserID. Then you could also have a date field, instead of having three different date fields in your but record.

Records are cheap, fields are expensive.