What I am trying to do is select all the rows out of the categories table (see below) and select the sum of j_amount out of the judgment table. Right now it is only returning the categories that are in the judgment table. I want it to return those fields with the amounts out of the judgment table, but also the all the other categories from the categories table with 0 as there amount. Does anyone have a suggestion.
select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle from categories LEFT OUTER JOIN judgment ON (j_category=ca_code) where j_case_no='45698' and j_party_no=1 group by ca_code,ca_desc,ca_dis_cycle
Thanks, Shaun
This left join won't work. Where clause changes your left join into inner one.
select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle from categories LEFT OUTER JOIN judgment ON (j_category=ca_code and j_case_no='45698' and j_party_no=1) group by ca_code,ca_desc,ca_dis_cycle
or
select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle from categories LEFT OUTER JOIN judgment ON (j_category=ca_code) where (j_case_no='45698' and j_party_no=1) or j_category is null group by ca_code,ca_desc,ca_dis_cycle
Probably you also need to change your sum(j_amount) into: sum(case when j_amount is null then 0 else j_amount end)
Regards, Tomasz Myrta
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
