SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast alvin101's Avatar
    Join Date
    Sep 2010
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    small problem adding data from second table

    Hi Guys,

    I have a small problem I just can not get a grip on...

    I have a table (inventory) that I would like to pull data from,

    When a new product is added to the database, rows are added to the inventory table, but because there is no history for these products there is no entries in the inventory_history table. The statement I have (second one below) works fine for data in the inventory_history table but I can not get it to read the new data from the inventory table.

    I need to add the results of the first statement to the results of the second statement. There is a cross over of data, the first statement will show some results that are not needed because there are entries for the same sku in the inventory_history table. I basically need to pull the new sku's from the inventory table that do not yet have entries in the inventory_history table and add them to the result of the second statement. The column inventory.quantity_on_hand is a little misleading, to get true quantity on hand, you must use SUM(inventory_history.remaining) see second statement. But because there is no history inventory.quantity_on_hand has a 0

    First statment
    Code:
    SELECT `sku`
    ,    `description_short`
    ,    `reorder_quantity`
    ,    `minimum_stock_level`
     
    FROM `inventory`
    
    WHERE
            `vendor_id` = 17
     AND    `quantity_on_hand` <= `minimum_stock_level`
    
    
    GROUP BY sku
    Second statment
    Code:
    SELECT inventory_history.sku
    ,  inventory.description_short
    ,SUM(remaining) AS "Qty On Hand"
    ,  inventory.minimum_stock_level
    ,  inventory.reorder_quantity
    ,  inventory.quantity_on_order
    ,  inventory.item_cost
    
    FROM inventory_history
     JOIN inventory
        ON inventory_history.sku = inventory.sku
    
    WHERE inventory_history.store_id = 4
      AND inventory.vendor_id = 10
    
    GROUP BY sku
    
    HAVING SUM(inventory_history.remaining)<(inventory.minimum_stock_level)
    How can I combine these statements?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sounds like a job for a LEFT OUTER JOIN ...
    Code:
      FROM inventory
    LEFT OUTER
      JOIN inventory_history
        ON inventory_history.sku = inventory.sku
    ...
     WHERE inventory_history.sku IS NULL -- will return inventory rows that do not have history
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast alvin101's Avatar
    Join Date
    Sep 2010
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy,

    Using this statement I get a (returned an empty result set)?

    Code:
    SELECT inventory_history.sku
    ,  inventory.description_short
    ,SUM(remaining) AS "Qty On Hand"
    ,  inventory.minimum_stock_level
    ,  inventory.reorder_quantity
    ,  inventory.quantity_on_order
    ,  inventory.item_cost
    
    FROM inventory
    LEFT OUTER
      JOIN inventory_history
        ON inventory_history.sku = inventory.sku
        
    WHERE inventory_history.sku IS NULL
      AND inventory_history.store_id = 4
      AND inventory.vendor_id = 17
    
    GROUP BY sku
    
    HAVING SUM(inventory_history.remaining)<(inventory.minimum_stock_level)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by alvin101 View Post
    Code:
    WHERE inventory_history.sku IS NULL
      AND inventory_history.store_id = 4
      AND inventory.vendor_id = 17
    unfortunately that's going to return 0 rows every time

    the IS NULL check was to ensure that only inventory rows ~without~ matching inventory_history rows are returned

    i was never able to wrap my head around how you wanted to combine rows for vendor 10 with rows for vendor 17

    i only mentioned the IS NULL tehcnique because that's what your original problem description suggested ...
    Code:
    SELECT inventory.sku
         , inventory.description_short
         , COALESCE(SUM(inventory_history.remaining),0) AS "Qty On Hand"
         , inventory.minimum_stock_level
         , inventory.reorder_quantity
         , inventory.quantity_on_order
         , inventory.item_cost
      FROM inventory
    LEFT OUTER
      JOIN inventory_history
        ON inventory_history.sku = inventory.sku
       AND inventory_history.store_id = 4
     WHERE inventory.vendor_id = 10
    GROUP 
        BY inventory.sku
    HAVING COALESCE(SUM(inventory_history.remaining),0) < inventory.minimum_stock_level
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast alvin101's Avatar
    Join Date
    Sep 2010
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, seems to work ok, sorry about the vendor id, it would be changed for the report you want ect....

    COALESCE is a new one on me, I shall learn about it...

    Thanks again,

    Alvin


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •