Two joins on same field

[Samnit]

Hey there - I'm trying to make the same join on two different fields in the same record. I have a field "sender" and a field "receiver", both contain user IDs. I want to select with this queary the usernames for both userIDs by joining on the users table, twice.

In effect, I am trying to accomplish the theoretical effect of this non-working snippet:

   SELECT ropasc_games.id,
          ropasc_games.sender,
          ropasc_games.receiver,
          users.username,
          users.username
     FROM ropasc_games
LEFT JOIN users ON ropasc_games.sender = users.id
LEFT JOIN users ON ropasc_games.receiver = users.id

Any solutions would be most appreciated - Better yet the specific documentation that discusses such an procedure. Thanks in advance!

 

[hos2]

you must create an alias for your user table since you reference it 2 times

SELECT ropasc_games.id,
          ropasc_games.sender,
          ropasc_games.receiver,
          us1.username,
          us2.username
     FROM ropasc_games
LEFT JOIN users AS us1 ON ropasc_games.sender = us1.id
LEFT JOIN users AS us2 ON ropasc_games.receiver = us2.id

something like this

 

[oharab]

or
SELECT ropasc_games.id,
ropasc_games.sender,
ropasc_games.receiver,
users.username,
users.username
FROM ropasc_games, users
WHERE
ropasc_games.sender = users.id AND
ropasc_games.receiver = users.id


hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------