Angelo Zanetti <[EMAIL PROTECTED]> 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
> -userID
> -isactive
> B BuddyList
> -userID
> -buddyID
> 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?
> TIA
> Angelo
Yes, the LEFT join is the correct choice for your query:
SELECT u.UserID
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.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
