CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Jayson Larner
Jayson Larner

Reputation: 73

SQL - Compare 2 tables and show non existent data

Ok, I have 2 tables that I need to do a comparison to. The idea is to show anyone in the database who has not purchased a specific product.

Table 1

UserID Customer ProductsSold  
1 John Cookies  
2 Susan Cake  
3 Jim Bread

Table 2

ProductCode ProductDesc  
Cookies 1 doz Cookies  
Cake  8-in cake  
Bread  Loaf of bread

What I am wanting to come back is

1 John Cake  
1 John Bread  
2 Susan Cookies  
2 Susan Bread  
3 Jim Cookies  
3 Jim Cake

So, I am stuck at figuring out the code as I don't have an ID match between the tables, only the Product name match. I know this is easy but I am drawing a blank right now.

Also, sorry for the poor formatting

Jayson

Upvotes: 0

Views: 150

Answers (2)

dnoeth
dnoeth

Reputation: 60482

Same logic as Gordon, but applying Set Operations:

select t1.UserID, t1.Customer, t2.ProductDesc
from table1 as t1       -- all possible User/Product combinations
cross join table2 as t2

except

select UserID, Customer, ProductsSold -- existing data
from table1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

General all possible combinations using cross join and filter out the ones that exist:

select n.id, n.name, t2.productcode
from (select distinct id, name from table1) n cross join
     table2 t2 left join
     table1 t1
     on t1.id = n.id and t2.productcode = t1.productsold
where t1.id is null;

Upvotes: 1

Related Questions