0
**OrdersTable**
  OrderID
  Product

SELECT OrderId, Product
FROM OrdersTable
WHERE Product = 'ProductA'
AND Product = 'ProductB' --doesn't work as AND looks for the same data in one row

OrderID   Product
-----------------
628       ProductB
628       ProductA

I am trying to query OrderID's that contain BOTH Product 'A' AND Product 'B'. I've tried using EXISTS, AND/OR, etc but they always return an either or situation (i.e. the query returns orders that have a Product 'A', but not a Product 'B' or vice versa). How can I query only OrderID's that contain both Products A and B?

2

You can use an aggregate for that:

SELECT OrderId
FROM OrdersTable
WHERE Product in ('ProductA', 'ProductB')
group by orderid
having count(distinct Product) = 2;

This will give you those orders that include at least those two products. The orders might include other products as well.

1

You need to combine results from 2 separate rows, so you need either JOIN or INTERSECT or something similar:

SELECT a.OrderId
FROM
        OrdersTable AS a
    JOIN
        OrdersTable AS b
    ON a.OrderId = b.OrderId
WHERE a.Product = 'ProductA'
  AND b.Product = 'ProductB' ;

SELECT OrderId
FROM OrdersTable
WHERE Product = 'ProductA'
INTERSECT
SELECT OrderId
FROM OrdersTable
WHERE Product = 'ProductB' ;

Using AND with EXISTS subqueries should work as well:

SELECT a.OrderId
FROM OrdersTable AS a
WHERE a.Product = 'ProductA'
  AND EXISTS
      ( SELECT 1
        FROM OrdersTable AS b
        WHERE a.OrderId = b.OrderId
          AND b.Product = 'ProductB'
      ) ;

And yes, there's also a method with IN and GROUP BY without subqueries, which nah, I won't bother adding.

0

why do not you try the IN clause. regards --QUERY

DECLARE @TESTTABLE TABLE(OrderId INT,Product NVARCHAR(50)) INSERT INTO @TESTTABLE VALUES(1,'ProductA') INSERT INTO @TESTTABLE VALUES(1,'ProductB') INSERT INTO @TESTTABLE VALUES(3,'ProductC') SELECT OrderId, Product FROM @TESTTABLE WHERE Product IN('ProductA','ProductB')

  • 1
    That will also return orders that only have one of those products. – a_horse_with_no_name Jul 4 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.