Tuesday, March 27, 2012

Derived Tables

OK...I know how to write a query to return for example :

All the people that ordered X and Y

but how do I write one for:

All the people that ordered X but not Y?

Thanks,
TreyO yea...this is how i did the first part


SELECT DISTINCT c.Company
FROM Customers as c
JOIN
(SELECT CustomerID
FROM Orders o
JOIN [Order Details] od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
WHERE p.ProductName = 'X') as temp1
ON c.CustomerID = temp1.CustomerID

JOIN
(SELECT CustomerID
FROM Orders o
JOIN [Order Details] od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
WHERE p.ProductName = 'Y') as temp2
ON c.CustomerID = temp2.CustomerID

No comments:

Post a Comment