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

[rdraider]

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

[Ed Murphy]

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
)

[rdraider]

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" <em*******@socal.rr.comwrote in message
news:46***********************@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
)

[Erland Sommarskog]

rdraider (rd******@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, es****@sommarskog.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

[rdraider]

Thanks for your help. A very good lesson my a newbie like me.
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

rdraider (rd******@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, es****@sommarskog.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