Cimplicated Join Query

[Leon1977]

Hi all I can not figure out a query. Any help would be appreciated.
So I have two tables Obligations and Payments
In Payments we have several or no rows pointing a single row in Obligations they look like this

Obligations Payments
----------- --------------
obligation_id payment_id
total fk_obligation_id
payment_summe
if_paid

ok so i need for every obligation_id to display its total
and sum(payment_summe) where if_paid=1 and
sum(payment_summe) where if_paid=2

 

[srishan]

Select   O.obligation_id, O.total, 
         sum(P.payment_summe) Pmnt_sum
From     Obligations O, Payments P
Where    O.obligation_id = P.fk_obligation_id
And      P.if_paid in (1, 2)
Group By O.oblidation_id, O.total;

Thx,
SriDHAR

 

[rrrkrishnan]

Try this :

select a.obligation_id,a.total,sum(b.payment_summe)
from obligations a left outer join payments b
on b.fk_obligation_id = a.obligation_id
where b.if_paid in (1,2)
group by a.obligation_id,a.total

If you do not need rows where there are no payment records for an obligation , try this :

select a.obligation_id,a.total,sum(b.payment_summe)
from obligations a , payments b
where b.fk_obligation_id = a.obligation_id
and b.if_paid in (1,2)
group by a.obligation_id,a.total

 

[Leon1977]

No you seem not to uderstand me
I need besides bligation_id, O.total
more two colums one sum(P.payment_summe) for all columns that that have if_paid =1
and onother one for if_paid=2
so I must have 4 colums as a result
and (srishan I need null values if we have no rows in Payments)

 

[rrrkrishnan]

This should work :

select dt1.obligation_id, dt1.total, dt1.sum1, dt2.sum2
from
(select a.obligation_id,a.total,sum(b.payment_summe) sum1
from obligations a, payments b
where a.obligation_id(+) = b.fk_obligation_id
and b.if_paid=1
group by a.obligation_id,a.total) dt1,
(select a.obligation_id,a.total,sum(b.payment_summe) sum2
from obligations a, payments b
where a.obligation_id(+) = b.fk_obligation_id
and b.if_paid=2
group by a.obligation_id,a.total) dt2
where dt1.obligation_id=dt2.obligation_id