DMZ 1.7.1 (DataMapper OverZealous Edition)

[eluser]Lord_Jago[/eluser]
Hi,

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.


Thanks,
Julien