Sql Query To Return Notification from store table

Question

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.

Consider normalising your database schema further to remove repeating field. Can you extract products to their own table — useyourillusiontoo, Oct 31 '16 at 07:59
Also extract branches to a table with the relevant branch info like notify limit — useyourillusiontoo, Oct 31 '16 at 07:59

score 1 · Accepted Answer · answered Oct 31 '16 at 08:00

1

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.

answered Oct 31 '16 at 08:00

RVid

1,207
1
14
31

Glad to hear it helps. Please accept my answer if it helped you to answer your question. – RVid Oct 31 '16 at 08:29
Read the following: [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers). – TT. Oct 31 '16 at 08:31

Sql Query To Return Notification from store table

1 Answers1