This is a simplified version of the actual tables and query I'm working with.
I feel like it shouldn't be that difficult, maybe i'm not seeing the forest for the trees.
I have a product table with 3 products in it.
I have a product_user table with "links" to the products. An entry for each user and product they are a user for.
(poor design, but a) this is a simplified version of the actual design and b) I'm stuck with this design)
I need every product_user and the products they are NOT linked as a user for.
If Object_ID('TempDB..#product_user', 'U') is not null
drop table #product_user
Create table #product_user(id int, username varchar(50), prod_id int)
If Object_ID('TempDB..#product', 'U') is not null
drop table #product
Create table #product(id int, product_name varchar(50))
Insert into #product (id, product_name)
Select 1, 'abc' UNION ALL
Select 2, 'xyz' UNION ALL
Select 3, 'def'
Insert into #product_user (id, username , product_id)
Select 1, 'jdoe', 1 UNION ALL
Select 2, 'asmith', 1 UNION ALL
Select 3, 'asmith', 3
I would like to select every product_user and the product they are NOT linked to.
Desired result -