selecting mutual results

[Phillip Parr]

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:

friends

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 :)

[Bill Karwin]

Phillip Parr wrote:

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))?

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)

Regards,
Bill K.

[Phillip Parr]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:d5********@enews3.newsguy.com...

Phillip Parr wrote:

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))?

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)

Regards,
Bill K.

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

Phil