Angelo Zanetti <angelo@stripped> wrote on 08/06/2005 17:06:51:
> 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?
LEFT JOIN sounds right to me:
SELECT a.* FROM a LEFT JOIN b ON a.userID = b.userID WHERE a.isactive = 1
AND b.buddyID IS NULL ;
All A A's which are active and do not have a buddy.
Alec

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.