I have a store
table which have productName
, productNumber
, storedInBranch
, blockNumber
, quantity
, notifyIn
.
If the quantity
is less than notifyIn
it will notify me and I tried the query below:
select * from store
where (quantity < notifyIn) AND (notify > 0)
which works perfect but since one product can be stored in multiple blockNumbers
it is notifying me even if the amount of products are not less than notifyin
,
eg.:
productName = monitor, productNumber=123, storedInBranch=kenya, blocknumber=5b, quantity=5, notifyin=4
productName = monitor, productNumber=123, storedInBranch=kenya, blocknumber=<b>1a</b>, quantity=5, notifyin=4
so the above query returns both as low what I wanted to do is sum both quantity
(5+5) and notifyIn
4.
You are relying here on notifyin being the same value in multiple rows. You could do something like
SELECT productNumber, sum(quantity), min(notifyin)
FROM store
GROUP BY productNumber
HAVING sum(quantity) < min(notifyin)
I would encourage you to change your schema if possible to better reflect your domain logic. Maybe have a separate table with product number and notify in value. That way you are not duplicating the notifyin
and risk storing different value in different rows.