Select values from second table that are not linked to first table
-
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
-
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); -
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.
-
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 nullUserId ProductId
----------- -----------
1 2
1 3
2 2
2 3
3 1
3 2
(6 rows affected) -
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 nullUserId ProductId
----------- -----------
1 2
1 3
2 2
2 3
3 1
3 2
(6 rows affected) -
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) -
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)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply