selecting non mutual results

[Phillip Parr]

hah well I'm here again, guess I need more practice to do this all properly!

I have a table, such as this:

friends

+--------+----------+
| userid | friendid |
+--------+----------+
| 2 | 7 |
| 2 | 3 |
| 2 | 6 |
| 2 | 8 |
| 2 | 9 |
| 2 | 34 |
| 7 | 2 |
| 34 | 2 |
| 9 | 2 |
| 2 | 47 |
+--------+----------+

and Bill K kindly helped with the code to fine opposite column matches (2
and 7, 7 and 2 for example) but now I want to return the results for a
specific user excluding these opposite column matches. How can I do this?

Thank you all again!

[cainlevy]

So you want all the pairs for a given user that don't have reciprocal
pairs? Is that right? For user 2, then, you'd want (2,3), (2,6), (2,8),
and (2,47)?

SELECT t1.*
FROM friends AS t1
LEFT JOIN friends AS t2
ON t1.userid = t2.friendid
AND t1.friendid = t2.userid
WHERE t1.userid = 2
AND t2.userid IS NULL

For pairs that -do- have reciprocals, you could switch the WHERE clause
to "t2.userid IS NOT NULL".

[Phillip Parr]

"cainlevy" <go***********@ cainlevy.net> wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...

So you want all the pairs for a given user that don't have reciprocal
pairs? Is that right? For user 2, then, you'd want (2,3), (2,6), (2,8),
and (2,47)?

SELECT t1.*
FROM friends AS t1
LEFT JOIN friends AS t2
ON t1.userid = t2.friendid
AND t1.friendid = t2.userid
WHERE t1.userid = 2
AND t2.userid IS NULL

For pairs that -do- have reciprocals, you could switch the WHERE clause
to "t2.userid IS NOT NULL".

That's great, thanks! It's shocking how close the solution was to what I
had. Must need more practice :)