|
|
Posted by rdraider on 05/07/07 20:32
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" <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
> )
>
Navigation:
[Reply to this message]
|