MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Rory McKinley Date:July 10 2004 5:21pm
Subject:Re: Finding the records in one table that are not in another table
View as plain text  
Jeff Gannaway 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  |
> +--------+-----+
>
> <snip>


Hi Jeff

What you are looking for is a LEFT JOIN - it would look something like this:

SELECT a.* FROM ProductsOLD a LEFT JOIN ProductsNEW b ON a.Vendor = 
b.Vendor AND a.ID = b.ID
WHERE b.ID IS NULL

HTH

Rory
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