List:General Discussion« Previous MessageNext Message »
From:Angelo Zanetti Date:June 8 2005 4:45pm
Subject:Re: LEFT JOIN?
View as plain text  
thanks shawn it seems to be working but i forgot to add that i need it
for a single user ID, in other words it must bring back all user ids in
the user table if they do not exist for that user in the buddylist.

so what i've tried is this:

SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID
WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null

but this still returns some results that exist in the buddyList table.


SGreen@stripped wrote:

>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
>>-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 
>  
>

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