Small problem adding data from second table

Databases
1

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


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


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

sounds like a job for a LEFT OUTER JOIN …

  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
3

Thanks Rudy,

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



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

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 …

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
5

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