Creating multiple joins accessing the same table

Question

I need a way to extract two user names from the same table. I'm able to pull the first name (jim), but I'm having trouble pulling from the second criteria/join.

The two tables:

tbl_users
usr_index |  usr_name
1         |  bob
2         |  mike
3         |  jim


tbl_master
mas_openedby  |  mas_closedby
    3         |        1
    1         |        3
    2         |        2

tbl_master.mas_openedby = 3
tbl_master.mas_closedby = 2

first results should be(opened by): jim sec. results should be(closed by): mike

select tbl.users.usr_name
...
FROM tbl_master

LEFT JOIN tbl_users ON tbl_users.usr_index = tbl_master.mas_openedby

LEFT JOIN tbl_users ON tbl_users.usr_index = tbl_master.mas_closedby

Answer 1

You need to assign each join a unique alias:

LEFT JOIN tbl_users AS userjoin1 ON userjoin1.usr_index = tbl_master.mas_openedby

LEFT JOIN tbl_users AS userjoin2 ON userjoin2.usr_index = tbl_master.mas_closedby

... then:

SELECT userjoin1.usr_name, userjoin2.usr_name FROM...