>>>Hi everyone,
>>>
>>>I am running the following select statement:
>>>
>>>Select Trans.cTrans_fk, Sum(Payments.yAmount_paid) as yAmount_paid, ;
>>>Sum(Charges.yCharges_amount) as yCharges_amount ;
>>>from Trans inner join Payments ;
>>>on Trans.cTrans_pk = Payments.cTrans_fk ;
>>>inner join Charges ;
>>>on Trans.cTrans_pk = Charges.cTrans_fk ;
>>>group by 1
>>>
>>>
>>>If I join two tables I have the right result but with three tables.
>>>Is there a problem joining more than two tables and using group by?
>>>
>>>Thanks.
>>
>>No. What data you have in these tables and what result you expect?
>>
>>Also are you sure that for each cTrans_pk you have at least one record in Payments and one record in Charges tables?
>
>Hi Borislav,
>
>This is what I trying to get:
>Trans_pk, yAmount_paid, yCharges_amount
>076245ACE0274CA6, 58981.2500, 59937.0000
>
>And every record in Trans may or may not have records in payments or charges.
Then make those both left joins instead of inner joins, and use nvl()... like this:
Select Trans.cTrans_fk, Sum(nvl(Payments.yAmount_paid,0)) as yAmount_paid, ;
Sum(nvl(Charges.yCharges_amount,0)) as yCharges_amount ;
from Trans ;
left join Payments ;
on Trans.cTrans_pk = Payments.cTrans_fk ;
left join Charges ;
on Trans.cTrans_pk = Charges.cTrans_fk ;
group by 1
With inner joins you see only transactions where you have records in all three tables for a given transaction.