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 1 year, 1 month ago by  pietlinden.
    • This reply was modified 1 year, 1 month 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

  • This was removed by the editor as SPAM

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

     

     

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

     

     

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

  • 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