I have folowwing three tables
1. OpeningStock
2. Material_Supplied
3. Material_Purchased
table openingstock contains Opening Balance of all 500 Items.
For example
ItemID ItemName Balance
--------------------------------
1 ABC 50
2 Def 100
3 ghi 32
4 jkl 500
5 mno 0
6 pqr 55
7 stu 0
8 vwx 0
table material_supplied contains Total quantity issued .
For example
ItemID Quantity
-------------------
2 60
4 220
6 10
table material_Purchased contains Total quantity Purchased.
For example
ItemID Quantity
-------------------
2 50
5 200
3 150
Now I want to create Stock summary report like
ItemID OpeningBalance Material_Supplied MaterialPurchased ClosingBalance
1 50 0 0 50
2 100 60 50 90
3 32 0 150 182
4 500 220 0 280
5 0 0 200 200
6 55 10 0 45
7 0 0 0 0
8 0 0 80 80
i had created query but it does not shows all items as it has been joined using 'inner join'
select o.itemid,o.openingStock,m.IssuedMaterial ,p.quantity,(o.openingStock - m.IssuedMaterial + p.quantity) as ClosingBalance from openingStock o inner join materialSupplied m on o.itemid=m.itemid
inner join materialPurchased p on o.itemid=s.itemid
order by o.itemid
it gives only one record of item id 2
so please help me guys...
thanx in advance.....