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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rdraider
    #1

    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



    Tags: None
    • Ed Murphy
      #2
      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
      )

        Comment

        • rdraider
          #3
          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" <emurphy42@soca l.rr.comwrote 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
          )
          >

            Comment

            • Erland Sommarskog
              #4
              Re: Query help: Item below reorder level-find all items for same vendor

              rdraider (rdraider@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, esquel@sommarsk og.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

                Comment

                • rdraider
                  #5
                  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" <esquel@sommars kog.sewrote in message
                  news:Xns9929F1A 74E76FYazorman@ 127.0.0.1...
                  rdraider (rdraider@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, esquel@sommarsk og.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/prodinf...ons/books.mspx

                    Comment

                    Previous template Next
                    Working...