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