I have a table USER which is self related (a many to many relationship). So I have the following structure :
USER : id, username...
SELFUSER : id, userA_id, userB_id
I'm looking for a way to do one of these 2 queries :
Code:
SELECT u.*
FROM users u
LEFT JOIN selfuser su
ON u.id = su.userA_id
AND su.userB_id = $user_id
Or,
Code:
SELECT u.*
FROM users u
LEFT JOIN selfuser su
ON u.id = su.userA_id
WHERE su.userB_id = $user_id
Impossible for me to find an easy way to do it. The only solution I've found is to use subqueries, which is quite complicated and dirty for such a simple query.