CODEWITHSUNDEEP
sql-serveradventureworks
porsh01
porsh01

Reputation: 95

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

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

Screenshot of my output

Upvotes: 1

Views: 1824

Answers (1)

shawnt00
shawnt00

Reputation: 17915

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.

Upvotes: 1

Related Questions