Slow Moving Stock Items Query

I am trying to return a list of slow-moving stock items. Whenever there is a sale, the items are written to the saleItems table. I want to get those items from inventoryMaster table that are not appearing in the saleItems table within the past N days. Here is my structure.

db_inventory - all inventory items are stored here in stockmain table
db_main - all sale transactions recorded here in saleitems table

below is the query which gives me an error

SELECT db_inventory.StockMain.ItemCode 
FROM 
db_inventory.StockMain
JOIN 
db_main.SaleItems 
ON 
db_main.SaleItems.ItemID=db_inventory.StockMain.ItemCode
WHERE NOT IN(
   SELECT db_main.SaleItems.ItemID FROM db_main.SaleItems 
   WHERE db_main.SaleItems.saledate BETWEEN 
   DATE_SUB(CURDATE(),INTERVAL 90 DAY) AND NOW()
)

Answer

Use NOT EXISTS with a correlated subquery.

SELECT ItemCode 
FROM db_inventory.StockMain i
WHERE NOT EXISTS (
    SELECT 1
    FROM db_main.SaleItems m
    WHERE m.ItemID = i.ItemID AND m.saledate > NOW() - INTERVAL 90 DAY
)

The subquery ensures that, for the given item in the inventory, no record exists in the sales table over the last 90 days.

Advertisement

Answer