Use the Northwind database Products table as an example.
Purchasing dept gets a report showing when inventory items on hand qty are
below the reorder level.
easy enough:
Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevel
from Products
where (UnitsInStock < ReorderLevel)

Results:
ProductID ProductName SupplierID UnitsInStock ReorderLevel
2 Chang 1 17
25
3 Aniseed Syrup 1 13
25


It would be nice to know what other products are purchased from this same
vendor in case other items are close to their reorder level.

All products for Supplier ID 1
Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevel
from Products
where SupplierID = 1

Results:
ProductID ProductName SupplierID UnitsInStock ReorderLevel
1 Chai 1 39
10
2 Chang 1 17
25
3 Aniseed Syrup 1 13
25


This shows there is 1 more product (Chai) that also comes from Supplier 1.
Is there a way to show all items from a vendor when some of the items are
below the reorder level without needing a separate query for each vendor?

Thanks

rdraider wrote:

Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevel
from Products
where SupplierID in (
select SupplierID
from Products
where UnitsInStock < ReorderLevel
)

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.



"Ed Murphy" <emurphy42 (AT) socal (DOT) rr.com> wrote

rdraider (rdraider (AT) sbcglobal (DOT) net) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks for your help. A very good lesson my a newbie like me.


"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote