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?

"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:46369d34$0$16675$4c368faf@roadrunner.com...
> 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
> )
>

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

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9929F1A74E76FYazorman@127.0.0.1...
> 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