List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:June 8 2005 3:54pm
Subject:Re: LEFT JOIN?
View as plain text  
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

Thread
LEFT JOIN?Angelo Zanetti8 Jun
  • Re: LEFT JOIN?SGreen8 Jun
    • Re: LEFT JOIN?Angelo Zanetti8 Jun
      • Re: LEFT JOIN?Michael Stassen8 Jun
        • Re: LEFT JOIN?Angelo Zanetti8 Jun
          • Re: LEFT JOIN?SGreen8 Jun
  • Re: LEFT JOIN?Alec.Cawley8 Jun
Re: LEFT JOIN?Michael Stassen8 Jun
  • Re: [SOLVED]LEFT JOIN?Angelo Zanetti9 Jun
    • Enhance a queryHarish Gabbita27 Jun
      • Re: Enhance a querySGreen27 Jun