[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_outs->where all the inward and outward transactions of particular items are inserted
The fields are->id(primary key)
Item_id
Date
transaction(whether inward/outward)
Price
Quantity
Total_amount
Average
supplier_Name(for inward)
invoice_Number(for inward)
opening_Stock
closing_Stock
2.master_transactions->This contains all the available items with/without
transactions...once any transaction occurs,the stock values
gets updated
The fields present are->trans_id(primary 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_transactions all the items will be available irrespective of transactions.Now 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.Items,case when a.Item_id=b.Item_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.Item_id then b.closing_Stock else a.stock end as clstock from master_transactions a left join master_in_outs b on a.Item_id=b.Item_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]

Originally Posted by 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.

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.Items,case when a.Item_id=b.Item_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.Item_id then b.closing_Stock else a.stock end as clstock from master_transactions a left join master_in_outs b on a.Item_id=b.Item_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.Item_id and b.Date='curdate()' ) clause is used...

Thanx for ur reply....