Posted by Erland Sommarskog on 05/07/07 21:45

rdraider (rdraider@sbcglobal.net) writes:
> Thanks for the help. Can I ask a follow up?
> What if you wanted to add the CategoryID to this so that the results
> showed UnitsInStock < ReorderLevel and included items where the supplier
> AND CategoryID were the same?
> Examples: SupplierID 7 has 5 items returned but only 1 is below reorder
> and all are different categories
> SupplierID 23 has 3 items returned but only 2 share the same
> CategoryID.

This is precisely why I prefer EXISTS over IN. IN can only handle when
the condition is on a single column. EXISTS is extensible:

Select a.ProductID, a.ProductName, a.SupplierID, a.CategoryID,
a.UnitsInStock, a.ReorderLevel
from Products a
where exists (
SELECT *
FROM Products b
WHERE a.SupplierID = b.SupplierID
AND a.CategoryID = b.CategoryID
AND b.UnitsInStock < b.ReorderLevel
)
ORDER BY a.SupplierID, a.CategoryID, a.ProductID


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]