selecting mutual results

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Hey all, I'm probably just having a lax moment but I can't quite figure this
out. Say I have a table such as this:


user    friend
1        2
1        3
2        4
2        1

how could i select results where user and friend are the same but in
opposite rows (that is, in this example, user 1 and friend 2 (user 2 and
friend 1))?

Thanks :)

Re: selecting mutual results

Phillip Parr wrote:
Quoted text here. Click to load it

SELECT f1.user, f2.friend
FROM friends AS f1 INNER JOIN friends f2
   ON (f1.user = f2.friend AND f1.friend = f2.user)
WHERE f1.user <= f1.friend

(the <= comparison at the end is to prevent pairs from appearing twice
in the result)

Bill K.

Re: selecting mutual results

Quoted text here. Click to load it

Ah yes, Thanks! f2.friend in the select statement needs to be f2.user to get
the result I wanted :)


Site Timeline