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

Answer

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;