Re: Finding the records in one table that are not in another table

[mos]

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/[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]