Posted by rdraider on 04/30/07 23:54

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

[Back to original message]