Angelo Zanetti <angelo@stripped> wrote on 06/08/2005 12:06:51 PM:
> Hi guys.
> I'm having a problem deciding whether a left join is suitable for what i
> want to do.
> I have two tables
> A Users
> B BuddyList
> what i want to do is to get all the users from A that don't exist as a
> buddyID for a user (buddyList) also the user must be active (isactive=1)
> but i cant get the correct result.
> Is the LEFT JOINcorrect for this operation or should i try using the NOT
> EXISTS command?
Yes, the LEFT join is the correct choice for your query:
FROM Users u
LEFT JOIN BuddyList bl
ON u.userID = bl.userID
WHERE u.isactive =1
AND bl.userID is null;
It's that last term (and bl.userID is null) that detects the non-matched
users across the LEFT JOIN.
Unimin Corporation - Spruce Pine