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

• jmadsen 98862

  SSC Eights!

  Points: 804

  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 5 days, 10 hours ago by  jmadsen 98862.

• pietlinden

  SSC Guru

  Points: 63293

  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 5 days, 9 hours ago by  pietlinden.
  • This reply was modified 5 days, 8 hours ago by  pietlinden.

• jmadsen 98862

  SSC Eights!

  Points: 804

  More actions

  January 23, 2023 at 11:34 pm

  #4141006

  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.

• January 24, 2023 at 7:22 am

  This was removed by the editor as SPAM