>Hi Everyone..
>
>I have 3 Tables
>
>
>First is Prodm
>---------------------------
>PdID| Name |OPStock
>----------------------------
>1 | aaaa | 10.00
>----------------------------
>2 | bbbb | 20.00
>----------------------------
>3 | cccc | 50.00
>----------------------------
>4 | dddd | 30.00
>----------------------------
>5 | eeee | 40.00
>----------------------------
>
>
>Second Is Production
>---------------------
>PdId |Date |Production
>-------------------------------
>2 |01/01/2010|100.00
>-------------------------------
>5 |01/01/2010|200.00
>-------------------------------
>1 |01/01/2010|100.00
>-------------------------------
>
>Third IS Sales
>-------------
>PdID|Date | Sales
>----------------------
>1 |01/01/2010| 5.00
>----------------------
>1 |01/02/2010|10.00
>----------------------
>3 |01/02/2010|50.00
>----------------------
>
>
>Now SQL Should Display Result as under..
>
>PdId |Name|OpStk|ProdQ|Sales|CLStock
>
> 1 |aaaa |10.00|100.00|15.00|095.00
> 2|bbbb |20.00|100.00|00.00|120.00
> 3|cccc |50.00|000.00|50.00|000.00
> 4|dddd |30.00|000.00|30.00|000.00
> 5|eeee |40.00|200.00|00.00|240.00
>
>
>
>Hope I will get help from u all..
>
>Rajesh
Seems to me quite simple select.
select P1.PdID, P1.Name, P1.OpStk, NVL(P.ProdQ,0) as ProdQ,
NVL(S.Sales,0) as Sales, P1.OpStk + NVL(P.Prod,0) - NVL(S.Sales,0) as CLStock
from Prodm P1 LEFT JOIN Production P on P1.PdID = P.PdID
LEFT JOIN (select PdID, sum(Sales) as Sales from Sales group by PdID) S on P.PdID = S.PdID
Prodm P1 LEFT
If it's not broken, fix it until it is.
My Blog