473,853 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Apr 30 '07 #1
4 6131
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
)
May 1 '07 #2
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*******@soca l.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
)

May 7 '07 #3
rdraider (rd******@sbcgl obal.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****@sommarsk og.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
May 7 '07 #4
Thanks for your help. A very good lesson my a newbie like me.
"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...
rdraider (rd******@sbcgl obal.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****@sommarsk og.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

May 7 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
4498
by: Bernie | last post by:
Greetings, I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call them parent, child1, and child 2. On parent, I create a view called item as follows: CREATE view Item as select * from child1.dbchild1.dbo.Item union all select * from child2.DBChild2.dbo.Item
3
4949
by: Damroo | last post by:
I have a table as below --------------------------- name linkcode level --------------------------- brando 1,3,8 1 damroo 1,5 2 rogers 2,7 1 shane 1,7 1
4
3583
by: Steve | last post by:
I have a products table where the PK is ProductID. Also have the standard Orders table and OrderDetails table. I created a query that joins the Orders table and OrderDetails table. The query includes the OrderDate field from the Orders table and the ProductID field from the OrderDetails table. I added a criteria to the OrderDate field to limit the orders to the past year. Then I converted the query to a totals query to get the total sold of...
6
2342
by: Nathan Plamondon | last post by:
I am writing a parts inventory database for my workplace. I have it mostly working, but the reordering report isn't quite right. I want the report to show me parts that need to be ordered. The query I'm using in the report to generate this number is: UnitsNeeded: Sum(nz()-(nz()-nz())) with a ">0" filter to limit the report to only parts I need to order. This is, essentially, subtracting units on hand from the reorder level.
1
270
by: kev | last post by:
Hi folks, Firstly, Happy New Year to all. I have some problems with query pls help.I have 4 tables namely Equipment,Safety1,Safety2,Safety3. some of the fileds in each table is shown below. equipment: requestorname,building,equipmentid(pk),WWID safety1:status,equipmentid safety2:requeststatus,equipmentid safety3:status3,equipmentid
1
2122
by: shauna | last post by:
hi, i am an As level student studying Applied ICT, im having problems with normalisation. our problem is to computerise a made up business.mine for example is a beauty salon. below are my attributes and they need to be normalised to 3rd normal form! i have also shown what i have come up with but i am unsure as to whether it is correct! please help as im finding this very difficult. Normalisation The goal of normalisation is to create a...
6
2023
by: ismomo | last post by:
Hi all, I am facing a problem in recursive. I would like to create node strcuture similar with the xml structure shown below. However, I am not able to create the node with the sub I written in perl. I got the error, Can't call method "appendChild" on an undefined value at the $top_xml_node->appendChild($sub_node);. Anybody can help me? I have stuck at this function. Thanks. <organizations default="TOC1"> <organization> ...
1
1848
by: tizmagik | last post by:
MS Access Database I'm working on a 'Reorder' function for my friend's database. Essentially, it is a 'deep-copy' of several related tables. Here is the table setup... tblSalesOrder SalesOrderID (PK) Field1, Field2, etc tblWorkOrder
9
1564
by: John Torres | last post by:
I am working with the Inventory Dbase and I have a “Reorder Level” field which the quantity varies. I also have a query field Qty On Hand Qty On Hand: Sum(!-!) I’m trying to make a query to show all "Qty On Hand" that is less than the "Reorder Level". How do I make that Statement. Thanks John
4
4595
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet reservation of available resources (laptops, beamers, etc). The MySql database queries are already in place, as is the ASP administration panel. The frontend that users will see however, still needs some work. I'm really close, but since I'm no...
0
9898
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10670
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7907
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7074
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5736
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5934
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4143
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3180
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.