Provide a unique list of customer id’s and account numbers which have ordered both products 711 and 712 after July 1, 2008

Question

The question is from AdventureWorks2008R2

I tried this query, but I only want Customers who ordered both the products 711 and 712. For example, CustomerID 11441 has ordered productid 711 and 712, so only 11441 shall be displayed

Select DISTINCT(oh.CustomerID), oh.AccountNumber, CAST(oh.OrderDate as DATE) OrderDates, od.ProductID
From Sales.SalesOrderHeader oh
Inner Join Sales.SalesOrderDetail od
ON od.SalesOrderID = oh.SalesOrderID
WHERE od.ProductID BETWEEN 711 AND 712
AND CAST(oh.OrderDate as DATE) > '2008-07-01'
ORDER BY oh.CustomerID 

Screenshot of my output

Answer 1

SELECT
    oh.CustomerID, oh.AccountNumber,
    CAST(oh.OrderDate as DATE) OrderDates, /* doesn't really make sense as a plural */
    od.ProductID
FROM Sales.SalesOrderHeader oh
WHERE CAST(oh.OrderDate as DATE) > '2008-07-01'
AND (
        SELECT COUNT(DISTINCT ProductID)
        FROM SalesOrderDetail od
        WHERE od.SalesOrderID = oh.SalesOrderID
            AND od.ProductID IN (711, 712)
    ) = 2
ORDER BY oh.CustomerID;

Here's one way using a correlated subquery. By the way, what you were trying to do with DISTINCT in your original query won't work.