From: SGreen
Date: June 8 2005 4:00pm
Subject: Re: LEFT JOIN?
List-Archive: http://lists.mysql.com/mysql/185171
Message-Id: <OF10432A63.7DD0BB88-ON8525701A.0057A6B0-8525701A.0058369B@unimin.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="=_alternative 0058369A8525701A_="

--=_alternative 0058369A8525701A_=
Content-Type: text/plain; charset="US-ASCII"

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 
--=_alternative 0058369A8525701A_=--