Select values from second table that are not linked to first table

Question

Post reply

Select values from second table that are not linked to first table

jmadsen 98862

SSC Eights!

Points: 914
More actions
January 23, 2023 at 9:22 pm
Go to Answer
#4140972
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 -
product_user, product_id
jdoe, 2
jdoe, 3
asmith, 2
- This topic was modified 1 year, 11 months ago by jmadsen 98862.
pietlinden

SSC Guru

Points: 63506
More actions
January 23, 2023 at 10:06 pm
Answer
#4140984
Sounds like you need to cross join the two tables (creates all possible combinations... be careful with these!), and then subtract out the matches. (It would help us a LOT if you would put code tags around your code... just makes it easier to read. Thanks for the sample data - that's a huge help.
The CROSS JOIN part creates a unique list of all possible combinations of (ProductIID, UserID), and then you basically subtract out all the values in the #product_user table by using EXCEPT, and what's left is your answer.
Ideally, you would have 3 tables, not 2. One for Users, one for Products, and one for "uses". Then it's just
(Users X Products) - Uses
```
use tempdb;
go

CREATE TABLE #product_user(
id int,
username varchar(50),
prod_id int);

CREATE TABLE #product(id int, product_name varchar(50));
GO

Insert into #product (id, product_name)
VALUES (1, 'abc'),(2, 'xyz'),(3, 'def');

Insert into #product_user (id, username , prod_id)
VALUES (1, 'jdoe', 1),
(2, 'asmith', 1),
(3, 'asmith', 3);

/* select every product_user and the product they are not using */-- returns 9 records
SELECT xj.*
FROM
(SELECT ProductID = id, u.UserID
FROM #Product 
CROSS JOIN
(SELECT DISTINCT UserID = id
FROM #product_user) 
u) xj
EXCEPT 
(SELECT p_u.prod_id, p_u.id
FROM #product_user p_u);
```
- This reply was modified 1 year, 11 months ago by pietlinden.
- This reply was modified 1 year, 11 months ago by pietlinden.
January 24, 2023 at 7:22 am

This was removed by the editor as SPAM
February 7, 2023 at 6:26 am

This was removed by the editor as SPAM

February 9, 2023 at 11:50 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

jmadsen 98862 SSC Eights! Points: 914 More actions · Answer 1

Thank you. This is what I needed. A cross join with an except.

I just modified for the username rather then the id and it returns exactly what I was looking for.

Applied it to my larger real life scenario and it works.

aitorzur@hotmail.com SSC Rookie Points: 32 More actions · Answer 2

I modified "pietlinden" solution to use a left join rather than EXCEPT. I find it more clear.

select xj.UserId, xj.ProductId
from (
select u.UserId,  ProductId = p.id
from ( select distinct UserId = id from #product_user ) u
cross join  #product p
) xj
left outer join #product_user pu
on xj.UserId = pu.Id
and xj.ProductId = pu.prod_id
where pu.Id is null

UserId      ProductId
----------- -----------
1           2
1           3
2           2
2           3
3           1
3           2

(6 rows affected)

aitorzur@hotmail.com SSC Rookie Points: 32 More actions · Answer 3

I modified "pietlinden" solution to use a left join rather than EXCEPT. I find it more clear.

select xj.UserId, xj.ProductId
from (
select u.UserId,  ProductId = p.id
from ( select distinct UserId = id from #product_user ) u
cross join  #product p
) xj
left outer join #product_user pu
on xj.UserId = pu.Id
and xj.ProductId = pu.prod_id
where pu.Id is null

UserId      ProductId
----------- -----------
1           2
1           3
2           2
2           3
3           1
3           2

(6 rows affected)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Never mind... post deleted because it wasn't necessary. The OP figured it out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

As a bit of a sidebar, this uses 31% fewer reads, which will come in handy for when the cross joined tables are larger...

 WITH 
 cteProduct AS (SELECT ProductID = ID FROM #Product)
,cteUser    AS (SELECT DISTINCT UserName = username FROM #product_user)
 SELECT u.UserName,p.ProductID
   FROM cteProduct p CROSS JOIN cteUser u
 EXCEPT
 SELECT username,prod_id
   FROM #product_user
  ORDER BY UserName, ProductID
;

Results:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)