Its a Lending Library where products which are currently out on loan on a particular date are displayed, if the product quantity is greater than the Lend total display it, if not display nothing (show no records) If the SUM/Count = 0 again dont display it.
I have 2 tables Lending & Products, when an item is loaned out a record is added to the Lending table containing Product_ID, Date on loan. The product table has the current Product Stock level called Product_QTY
Heres my attempt:
SELECT dbo.Products.Product_QTY - COUNT(dbo.Lending.Lend_ID) as Lend_Total
FROM (dbo.Lending INNER JOIN dbo.Products ON dbo.Lending.Product_ID = dbo.Products.Product_ID)
WHERE dbo.Lending.Product_ID = 18 AND Dates = '11/09/2022'
GROUP BY dbo.Lending.Product_ID, dbo.Products.Product_QTY, Lend_Total
HAVING Lend_Total < 1
The issue is that even if the stock level is zero it still display's the record show zero as the stock level
Lending Table: Lend_ID Product_ID Date
Products Table: Product_ID Product_QTY
I have been stuck on this problem for a few weeks now, gratful for any help please, thank you.
