CODEWITHSUNDEEP
sql-serversql-server-2008
Mike
Mike

Reputation: 2601

SQL Server 2008 - Query which matches both conditions

I'm trying to find customer's (CustomerID) that have both 1 and 4 for remaining weeks. I'm using the following query:

SELECT  o.CustomerID, oi.RemainingWeeks
FROM    Orders o INNER JOIN
        OrderItems oi ON o.OrderID = oi.OrderID 
WHERE   (oi.OrderItemStatusID = 1) AND (oi.RemainingWeeks IN (1, 4)) 
ORDER BY o.CustomerID, oi.RemainingWeeks

This finds customers who have 1 for remaining weeks or 4 but not ones that have both. Do I need to modify my join to make this work?

Thanks!

Upvotes: 1

Views: 634

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

The key thing to realise is that there's never going to be a single row from OrderItems that has RemainingWeeks equal to both 1 and 4. So you need to involve multiple rows somehow. You might achieve this through aggregates, or just performing a second join:

SELECT  o.CustomerID, oi1.RemainingWeeks,oi2.RemainingWeeks
FROM    Orders o INNER JOIN
        OrderItems oi1 ON o.OrderID = oi1.OrderID INNER JOIN
        OrderItems oi2 ON o.OrderID = oi2.OrderID
WHERE   (oi1.OrderItemStatusID = 1) AND (oi1.RemainingWeeks =1) AND
        (oi2.OrderItemStatusID = 1) AND (oi2.RemainingWeeks =4) 
ORDER BY
     o.CustomerID, oi1.RemainingWeeks,oi2.RemainingWeeks

Upvotes: 6

Martin Smith
Martin Smith

Reputation: 453028

This is a relational division problem. The way to solve it via aggregates is

SELECT  o.CustomerID
FROM    Orders o INNER JOIN
        OrderItems oi ON o.OrderID = oi.OrderID 
WHERE   (oi.OrderItemStatusID = 1) AND (oi.RemainingWeeks IN (1, 4)) 
GROUP BY o.CustomerID
HAVING COUNT(DISTINCT oi.RemainingWeeks) = 2
ORDER BY o.CustomerID

Upvotes: 3

Related Questions