Rewriting mysql subqueries with LEFT JOIN

Question

Hello

I have a table with such structure:

• uid
• sid

all integers. This table simulates friendship between two user.

All I want to do is to find such records that exist only in one direction.

For example: I want to find number of records that have t1.u_id = 15, when there is no records that have t2.s_id = 15 and t2.u_id = t1.s_id.

So that relationship between two ids exists only once.

It seemed to me that I've managed to write the correct query with subqueries, but then I've started to rewrite the query using left join to increase speed and now...well it's not working.

My query right now is:

SELECT * FROM `friends` AS f1 LEFT JOIN `friends` AS f2 
ON f1.user_id = f2.friend_id 
WHERE f1.friend_id=15 AND f2.user_id IS NULL

but it's not working. Please help.

NB. I'm using mysql 5, so I can't use EXCEPT statement and all other good things.

Answer 1

How about this?

SELECT * 
FROM `friends` AS f1 
    LEFT JOIN `friends` AS f2 ON f1.user_id = f2.friend_id AND f1.friend_id = f2.user_id
WHERE f1.friend_id=15 
    AND f2.user_id IS NULL

Answer 2

And this one ?

SELECT * FROM friends f1 LEFT JOIN friends f2 ON f1.user_id = f2.friend_id WHERE f1.friend_id=15 AND f2.user_id IS NULL