Posted by Ed Murphy on 05/01/07 01:50

rdraider wrote:

> 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?

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

[Back to original message]