Query help: Item below reorder level-find all items for same vendor

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

Re: Query help: Item below reorder level-find all items for samevendor

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
)

Re: Query help: Item below reorder level-find all items for same vendor

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" 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
> )
>

Re: Query help: Item below reorder level-find all items for same vendor

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Query help: Item below reorder level-find all items for same vendor

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


"Erland Sommarskog" 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/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx