List:General Discussion« Previous MessageNext Message »
From:mos Date:July 10 2004 5:26pm
Subject:Re: Finding the records in one table that are not in another
table
View as plain text  
Jeff,
         You need to use a Left Join which will join rows from the second 
table even if the rows from the second table does not exist, and returns 
NULL for all column for the second table.. You then use the Where clause to 
check for a particular Table2.field that should exist (like Id column) and 
if it doesn't then the row in table 2 does not exist.

Example:

select po.id from ProductsOld po left join ProductsNew pn on po.id=pn.id 
where pn.id is null

This will list only po.id where it does not exist in pn.

If you have more than a hundred rows, there should be some sort on index on 
'id' in both tables so the join can use an index.

Mike
http://dev.mysql.com/doc/mysql/en/JOIN.html

At 11:03 AM 7/10/2004, you wrote:
>I have 2 tables - ProductsOLD and ProductsNEW.  I need to find the records 
>that are in the ProductsOLD table and are NOT in ProductsNEW (this will 
>tell me which products have been discontinued).
>
>Here's some sample data:
>+==============+
>| ProductsOLD  |
>+==============+
>+ Vendor | ID  |
>+--------+-----+
>| AAD    | 1   |
>| AAD    | 2   |
>| AAD    | 3   |*
>| BBD    | 1   |*
>| BBD    | 2   |
>| BBD    | 3   |*
>+--------+-----+
>(* = these are the products that are NOT in the ProductsNEW table)
>
>
>+==============+
>| ProductsNEW  |
>+==============+
>+ Vendor | ID  |
>+--------+-----+
>| AAD    | 1   |
>| AAD    | 2   |
>| AAD    | 5   |
>| BBD    | 2   |
>| BBD    | 7   |
>| BBD    | 10  |
>+--------+-----+
>
>I need to know the query that would result in:
>
>+==============+
>+ Vendor | ID  |
>+--------+-----+
>| AAD    | 3   |
>| BBD    | 1   |
>| BBD    | 3   |
>+--------+-----+
>
>There is no primary key for either ProductsNEW or ProductsOLD.  The data 
>comes from our distributor, and they don't have any field which would be 
>unique from record to record.  If a primary key is essential, I can 
>pre-process the tables to create one.
>
>Thanks in advance for your help!!!
>-Jeff Gannaway
>_______________________________________________
>
>http://RadioU.com
>This Is Where Music Is Going - Listen Online!
>_______________________________________________
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Finding the records in one table that are not in another tableJeff Gannaway10 Jul
  • Re: Finding the records in one table that are not in another tableRory McKinley10 Jul
  • Re: Finding the records in one table that are not in anothertablemos10 Jul