Post reply

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

  • September 20, 2019 at 1:13 pm

    #3681887

    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.

  • September 20, 2019 at 1:28 pm

    #3681895

    I would go for

    select OrderId from OrderDetails where ProductId=1
    intersect
    select OrderId from OrderDetails where ProductId=2;

  • September 20, 2019 at 1:56 pm

    #3681905

    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

  • September 20, 2019 at 3:39 pm

    #3681959

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

    MVDBA

  • September 20, 2019 at 3:43 pm

    #3681961

    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

  • September 20, 2019 at 4:07 pm

    #3681976

    MVDBA (Mike Vessey) 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

  • September 20, 2019 at 5:27 pm

    #3682045

    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 6 (of 6 total)

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