>>>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?
>
>If we don't have records in Payments or Charges, should this select be re-written to use LEFT JOIN and NVL instead?
>
>Thanks.

It depends what you want. If you want to get only these records from main table that have match in BOTH other tables, you must use INNER JOIN, but if you want to get ALL records from main table, no matter if they have corresponding records in other table you must use LEFT JOIN.

CREATE CURSOR crsTest  (Fld1 I)
CREATE CURSOR crsTest1 (Fld1 I)
CREATE CURSOR crsTest2 (Fld1 I)

INSERT INTO crsTest VALUES (1)
INSERT INTO crsTest VALUES (2)
INSERT INTO crsTest VALUES (3)

INSERT INTO crsTest1 VALUES (1)
INSERT INTO crsTest1 VALUES (3)

INSERT INTO crsTest2 VALUES (2)

SELECT * FROM crsTest;
INNER JOIN crsTest1 ON crsTest.Fld1 = crsTest1.Fld1;
INNER JOIN crsTest2 ON crsTest.Fld1 = crsTest2.Fld1
** (no records in the query)


SELECT * FROM crsTest;
LEFT JOIN crsTest1 ON crsTest.Fld1 = crsTest1.Fld1;
LEFT JOIN crsTest2 ON crsTest.Fld1 = crsTest2.Fld1
*** all records from crsTest with NULLs in other where there is no match