Doubt in querying

[divyac]

I am doing a project on inventory control of grocery items using PHP and mysql 5.In my project i have 2 tables..
1.master_in_out s->where all the inward and outward transactions of particular items are inserted
The fields are->id(primary key)
Item_id
Date
transaction(whe ther inward/outward)
Price
Quantity
Total_amount
Average
supplier_Name(f or inward)
invoice_Number( for inward)
opening_Stock
closing_Stock
2.master_transa ctions->This contains all the available items with/without
transactions... once any transaction occurs,the stock values
gets updated
The fields present are->trans_id(prima ry key)
Item_id
Items
stock
avgPrice
Date
The difference between these 2 tables is that in master_in_outs only the items which are transacted are available but in master_transact ions all the items will be available irrespective of transactions.No w i am getting a problem while generating a transactions report on a particular date..
The report should generate all the items and all the transactions on the given date...
My query is as follows...

select DISTINCT a.Item_id,a.Ite ms,case when a.Item_id=b.Ite m_id then b.opening_Stock else a.stock end as opstock,case when b.transaction=2 then b.Quantity else 0 end as inward,case when b.transaction=1 then b.Quantity else 0 end as outward,case when a.Item_id=b.Ite m_id then b.closing_Stock else a.stock end as clstock from master_transact ions a left join master_in_outs b on a.Item_id=b.Ite m_id AND b.Date=curdate( )

[r035198x]

What does your current query give and what did you want it to return instead?
Note also that keeping a current quantity of inventory may not be the best approach. You'd have to update that value after each transaction. Rather always deduce the current quantity by querying the transactions table. Your transactions table could use -q for quantity q issued out and +q for quantity q coming in.

[divyac]

What does your current query give and what did you want it to return instead?
Note also that keeping a current quantity of inventory may not be the best approach. You'd have to update that value after each transaction. Rather always deduce the current quantity by querying the transactions table. Your transactions table could use -q for quantity q issued out and +q for quantity q coming in.

But i have to show each and every transaction in the report and so i need to keep all the records without appending the values..

Yes i do use +q and -q for inward and outward at the time of insertion...

And now my problem got solved with a simple "where" clause..
select a.Item_id,a.Ite ms,case when a.Item_id=b.Ite m_id then b.preStock else a.stock end as opstock,case when b.transaction=2 then b.Quantity else 0 end as inward,case when b.transaction=1 then b.Quantity else 0 end as outward,case when a.Item_id=b.Ite m_id then b.closing_Stock else a.stock end as clstock from master_transact ions a left join master_in_outs b on a.Item_id=b.Ite m_id and b.Date='curdate ()' where a.Date='curdate ()'....

previously i was getting all the transactions in all dates...Now the problem is solved..I thought i couldnot use "where" if "on"(on a.Item_id=b.Ite m_id and b.Date='curdate ()' ) clause is used...

Thanx for ur reply....