Best way to write query that shows orders that\'ve purchased 2 specific products?

  • Goalie35

    Say Hey Kid

    Points: 696

    Basically, I just want to write a cleaner query than what I have, which involved setting up 2 temp tables.  I'm reviewing orders placed in our company.  Our OrderDetails table contains 2 key fields:

    • the order id
    • the product id that was ordered

    So obviously, there will be one record in this table for each product purchased.

    I want to create a query that returns the order id's of all orders that've purchased ProductId =1 AND ProductId = 2.

    OrderDetails

    OrderDetailsId                   OrderId                  ProductId

     


    1                                         123                             1

    2                                         456                             1

    3                                         456                             3

    4                                         789                            1

    5                                         789                            2

    6                                         789                            3

    In the above scenario, only orderId 789 should be returned because it's the only order that contains both productId's 1 & 2.

    Thanks for any help you can provide.

  • Andreas P. Williams

    SSCrazy

    Points: 2177

    I would go for

    select OrderId from OrderDetails where ProductId=1
    intersect
    select OrderId from OrderDetails where ProductId=2;
  • drew.allen

    SSC Guru

    Points: 76604

    Since you didn't provide sample data IN A CONSUMABLE FORMAT, you're getting untested code.  This might perform better.

    SELECT OrderID
    FROM OrderDetails
    WHERE ProductID IN (1, 2)
    GROUP BY OrderID
    HAVING COUNT(DISTINCT ProductID) = 2;

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • MVDBA

    SSC-Insane

    Points: 20823

    drew is correct (I mean his code is the right way to do it)

    • This reply was modified 1 month ago by  MVDBA.

    MVDBA

  • MVDBA

    SSC-Insane

    Points: 20823

    although in your example order 789 contains 3 items, do you only want where there are 2 items? both of which are 1 and 2? or if you have 50 items (including 1 and 2) is that acceptable?

    MVDBA

  • drew.allen

    SSC Guru

    Points: 76604

    MVDBA wrote:

    although in your example order 789 contains 3 items, do you only want where there are 2 items? both of which are 1 and 2? or if you have 50 items (including 1 and 2) is that acceptable?

    His original description already said that 789 should be returned.  The extra item(s) clearly do not matter.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Goalie35

    Say Hey Kid

    Points: 696

    or if you have 50 items (including 1 and 2) is that acceptable?

    Correct.  Even though order id 789 contains 3 items, it should be included because Product ID's 1 & 2 are included

    Thanks again!

Viewing 7 posts - 1 through 7 (of 7 total)

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