[SOLVED] Join a table more than once from a single table?

[iamgregg]

I'm fine with normal join queries, but I'm having trouble with this one. I don't think I explained it well in the topic title so I'll go into more detail.

Two tables:
users
pairs

Table users has details about said users.
The pairs table contains the 2 fields: user_1 and user_2, which are the respective users IDs.

In one qury I want to grab the name of each user. I can do it fine for one of them but not sure how to both, e.g. how would I expand this?

SELECT pairs.user_1, users.name
FROM pairs, users
WHERE pairs.user_1 = users.id

I can't figure out how to do it for both? The furthest I get is

SELECT pairs.user_1, pairs.user_2, users.name
FROM pairs, users
WHERE pairs.user_1 = users.id AND pairs.user_2 = users.id

I know it's wrong.

Anyone know?

Thanks.

[Tyche]

You can achive this by adding the table `users` in the FROM section twice but using a different  alias each time

e.g.


SELECT pairs.user_1, users_1.name, pairs.user_2, users_2.name
FROM pairs, users AS users_1,users AS users_2
WHERE  pairs.user_1 = users_1.id AND pairs.user_2 = users_2.id

[iamgregg]

I just found the solution from someone else but thankyou for replying anyway.

[i][b]fenway: if that's the case, post it here for all to see![/b][/i]