 | | | Not In join query. | Not In join query. 2007-10-13 - By Baron Schwartz
Back
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
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|