# selecting non mutual results

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

•  Subject
• Author
• Posted on

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!

## Re: selecting non mutual results

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".

## Re: selecting non mutual results

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