From: Baron Schwartz (baronxaprb.com)
Date: Sat Oct 13 2007 - 17:16:19 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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/how-to-write-a-sql-exclusion-join/

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

Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]