SEO

#1 (**permalink**) 02-28-2008, 07:01 AM

I have 2 queries to give me a list of names.

Q1:
SELECT DISTINCT FName, LName
FROM user u
JOIN userprofile p
USING ( UserID )
JOIN trainingstatus t
USING ( UserID )
WHERE ProgramID =12
ORDER BY LName, FName

Q2
SELECT DISTINCT FName, LName
FROM namelist
WHERE `Date`

What I need is query that will give me a list of names that are in the
Q2 result but not in the Q1 result.
This is easy enough if I am just doing the match on one filed I can do this

SELECT Name
FROM
namelist
WHERE `Date` AND Name NOT IN(
SELECT Name
FROM . . . . . . )

What I can't figure out is how to do it if I want to match of FName and
LName. I tried to use concat to build the full name and do the not in
based on the new field MySQL didn't like that query at all.

--
Chris W
KE5GIX

"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm"

#2 (**permalink**) 02-28-2008, 07:01 AM

Hi Chris,

Chris W wrote:
> I have 2 queries to give me a list of names.
>
> Q1:
> SELECT DISTINCT FName, LName
> FROM user u
> JOIN userprofile p
> USING ( UserID )
> JOIN trainingstatus t
> USING ( UserID )
> WHERE ProgramID =12
> ORDER BY LName, FName
>
> Q2
> SELECT DISTINCT FName, LName
> FROM namelist
> WHERE `Date`
>
> What I need is query that will give me a list of names that are in the
> Q2 result but not in the Q1 result. This is easy enough if I am just
> doing the match on one filed I can do this
>
> SELECT Name
> FROM
> namelist
> WHERE `Date` AND Name NOT IN(
> SELECT Name
> FROM . . . . . . )
>
> What I can't figure out is how to do it if I want to match of FName and
> LName. I tried to use concat to build the full name and do the not in
> based on the new field MySQL didn't like that query at all.

This is easier to do with an exclusion join:
http://www.xaprb.com/blog/2005/09/23...xclusion-join/

It is also much more efficient in current versions of MySQL.

Baron

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Not In join query.