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);

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

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

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