Joining the same table twice

Databases
1

Have a table in which I need to get two user’s usernames from it. I.e.

challenge(id, challengerid, receiverid)

challengerid and receiverid are ids from the member table.

member(id, username)

how do I get both the challengerid’s username aswell as the receiverid’s username with the same query?

Any help would be awesome!

2

Awesome thanks guys!

3

Just like you say in the title… join the same table twice, using aliasses to give them a unique name in the query:


FROM challenge
INNER JOIN members AS m1 
ON...
INNER JOIN members AS m2
ON...
4

i don’t like the generic aliases when there are much nicer ones available

SELECT challenge.id
     , challenger.username AS challenger
     , receiver.username AS receiver
  FROM challenge
INNER
  JOIN member AS challenger
    ON challenger.id = challenge.challengerid
INNER
  JOIN member AS receiver
    ON receiver.id = challenge.receiverid

i like this better than m1 and m2