Tricky problem... getting 2 usernames from 1 table

[dannyb785]

So here's my situation: I have a table called User and Report. The Report table has 2 columns, 1 for the person submitting the report and another for the user they are reporting. Each column contains the user id of the respective user. The User table has user_id and user_username. I know how to do a simple Join to get one of the usernames, but how do I get both?

 

For example: to get the username of the person submitting the report, my query is:

SELECT r_id,user_username,r_reason FROM Report,User WHERE User.user_id=Report.r_reporter

 

This gives me results with theusername who submitted the report, but I also need to grab the username of the person being reported

 

 

 

[mikosiko]

using different table aliases you can use (join) the same table more than once

[Barand]

EG

 

SELECT r.R_id, a.user_username as userA, b.user_username as userB
FROM report r
INNER JOIN user a ON r.userid1 = a.userid
INNER JOIN user b ON r.userid2 = b.userid

 

I don't know your column names so can't be precise. Join one id col to user with one alias and the second id to user with another alias

[mikosiko]

I call that ^^^ "instant gratification" ... no challenge for the OP at all...

[Barand]

But I have shown him a far better way to define joins rather than

 

... FROM tableA, tableB WHERE tableA.something = tableB.something

 

thus separating the structure of  the query from the selection conditions

[dannyb785]

EG

 

SELECT r.R_id, a.user_username as userA, b.user_username as userB
FROM report r
INNER JOIN user a ON r.userid1 = a.userid
INNER JOIN user b ON r.userid2 = b.userid

 

I don't know your column names so can't be precise. Join one id col to user with one alias and the second id to user with another alias

 

yes! this worked perfectly, thank you!!