mysql - Manipulate differents tables and display information from one

Question

Write an SQL statement to show the warehouse information for warehouses which do not have all SKU items in inventory

I tried this query but results: NULL

SELECT SKU
FROM SKU_DATA
WHERE NOT EXISTS
      (SELECT 1 FROM INVENTORY 
         JOIN WAREHOUSE  ON INVENTORY.WAREHOUSEID = WAREHOUSE.WAREHOUSEID
        WHERE SKU_DATA.SKU =INVENTORY.SKU);

Here the different tables available

Inventory (WarehouseID,SKU,SKU_Description,QuantityOnHand,QuantityOnOrder)

Order_Item(OrderNumber,SKU,Quantity,Price,ExtendedPrice)

Retail_order(OrderNumber,StoreNumber,StoreZip,OrderMonth,OrderYear,OrderTotal)

SKU_Data(SKU,SKU_Description,Department,Buyer)

Warehouse(WarehouseID,WarehouseCity,WarehouseState,Manager,SquareFeet)

enter image description here

question from:https://stackoverflow.com/questions/66065568/manipulate-differents-tables-and-display-information-from-one

Answer 1

Probably the easiest way will be just to count the number of unique SKUs in the SKU_Data, then select all the WarehouseID from the Inventory that have less than that number of unique SKUs.

Critically, this assumes a warehouse doesn't have a SKU that is missing from the SKU table.

Also assuming Inventory(WarehouseID,SKU) & SKU_Data(SKU) are unique

select WarehouseID from Inventory group by SKU
having count(*) < (select count(*) from SKU_Data)

I've not tested this, but I think it's about right :)

If this doesn't work, you can always select the count(*) into a local variable, something like

select @skunum := count(*) from SKU_Data
select WarehouseID from Inventory group by SKU having count(*) > @skunum

If you want to also check the warehouse has the item in stock, just add where QuantityOnHand > 0, after from Inventory