rai nalasa rai nalasa - 1 year ago 47
MySQL Question

how to get multiple rows using select?

I am trying to track all the changes that happened on my table

I tried this query

Update Stocks
Set stockOut = 100
,TrackDate = '1/30/2016'
,stockOnHand = stockOnHand - 400
WHERE itemID = '4589-S';

and this one.Both on Different date.

Update Stocks
Set stockOut = 200
,TrackDate = '2/30/2016'
,stockOnHand = stockOnHand - 400
WHERE itemID = '4589-S';

Now I used this query

from Stocks
where TrackDate between '1/30/2016' and '5/30/2016'

to track the changes within the specific span of
date.But I'm only getting 1 result only

I was Expecting to get 2 results because I've Updated this table in two different dates.

Answer Source

You need two tables,


When inserting/updating stocks, you should also create a new record in the STOCK_HISTORY table. The columns of this table should be something like ID, STOCK_ID, ACTION (create, update, delete) and a TIMESTAMP.

This way, you have no useless info in the STOCK table, and whenever you want to list all changes of a stock you do:

SELECT * FROM stocks s
  JOIN stock_history sh ON sh.stockId = s.id
  WHERE s.id = $stockId