Left outer join on multiple tables

Question

I have the following sql statement:

    select  
    a.desc
   ,sum(bdd.amount)
   from t_main c 
   left outer join t_direct bds on (bds.repid=c.id) 
   left outer join tm_defination def a on (a.id =bds.sId)
   where c.repId=1000000134
   group by a.desc;

When I run it I get the following result:

   desc       amount
   NW         12.00
   SW         10

When I try to add another left outer join to get another set of values:

   select  
    a.desc
   ,sum(bdd.amount)
   ,sum(i.amt)
   from t_main c 
   left outer join t_direct bds on (bds.repid=c.id) 
   left outer join tm_defination def a on (a.id =bdd.sId)
   left outer join t_ind i on (i.id=c.id)
   where c.repId=1000000134
   group by a.desc;

It basically doubles the amount field like:

         desc    amount   amt
         NW       24.00   234.00
         SE       20.00   234.00

While result should be:

        desc   amount   amt
        NW      12.00   234.00
        SE      10.00   NULL 

How do I fix this?

Answer 1

If you really need to receive the data as you mentioned, your can use sub-queries to perform the needed calculations. In this case you code may looks like the following:

select x.[desc], x.amount, y.amt
from
(
    select
         c.[desc]
       , sum (bdd.amount) as amount
       , c.id
    from t_main c 
    left outer join t_direct bds on (bds.repid=c.id) 
    left outer join tm_defination_def bdd on (bdd.id = bds.sId)
    where c.repId=1000000134
    group by c.id, c.[desc]
) x
left join
(
    select t.id, sum (t.amt) as amt 
    from t_ind t
    inner join t_main c
      on t.id = c.id
    where c.repID = 1000000134
    group by t.id
) y 
 on x.id = y.id

In the first sub-select you will receive the aggregated data for the two first columns: desc and amount, grouped as you need. The second select will return the needed amt value for each id of the first set. Left join between those results will gives the needed result. The addition of the t_main table to the second select was done because of performance issues.

Another solution can be the following:

select
     c.[desc]
   , sum (bdd.amount) as amount
   , amt = (select sum (amt) from t_ind where id = c.id)
from #t_main c 
left outer join t_direct bds on (bds.repid=c.id) 
left outer join tm_defination_def bdd on (bdd.id = bds.sId)
where c.repId = 1000000134
group by c.id, c.[desc]

The result will be the same. Basically, instead of using of nested selects the calculating of the amt sum is performing inline per each row of the result joins. In case of large tables the performance of the second solution will be worse that the first one.