Multiple Left Join on same table

Question

I have two table. 1st table => member {member_id, name, active} 2nd table => savings {savings_id, member_id, month, year, amount, type, paid} Member Table ``` +-----------+--------+--------+ | member_id | name | active | +-----------+--------+--------+ | 105 | Andri | 1 | | 106 | Steve | 1 | | 110 | Soraya | 1 | | 111 | Eva | 1 | | 112 | Sonia | 1 | +-----------+--------+--------+ ``` Savings Table ``` +------------+-----------+-------+------+--------+------+------+ | savings_id | member_id | month | year | amount | type | paid | +------------+-----------+-------+------+--------+------+------+ | 1 | 120 | NULL | NULL | 150000 | 1 | 1 | | 14 | 105 | 7 | 2014 | 80000 | 2 | 1 | | 15 | 105 | 7 | 2014 | 25000 | 3 | 1 | | 16 | 105 | 7 | 2014 | 60000 | 4 | 1 | | 17 | 105 | 7 | 2014 | 100000 | 5 | 1 | | 18 | 106 | 7 | 2014 | 80000 | 2 | 1 | | 19 | 106 | 7 | 2014 | 25000 | 3 | 1 | | 20 | 106 | 7 | 2014 | 60000 | 4 | 1 | | 21 | 106 | 7 | 2014 | 100000 | 5 | 1 | | 31 | 110 | 7 | 2014 | 25000 | 3…

Accepted Answer

The problem is you're forming the full join product before summation. So if there is more than one row in more than one of your `savings` tables you will end up with duplication. If you perform the join without summation, you can clearly see what is going on. There are two ways around this.

Do all summations in derived tables:

```
SELECT m.member_id, name, 
s1.amount as savings1,
s2.amount as savings2,
...
FROM members m
LEFT JOIN (
    select SUM(amount) as amount, member_id
    from savings
    where type = 1 and paid = 1
    group by member_id
) s1 ON s1.member_id = m.member_id
LEFT JOIN (
    select SUM(amount) as amount, member_id
    from savings
    where type = 2 and paid = 1
    group by member_id
) s2 ON s2.member_id = m.member_id
...
WHERE active = 1
GROUP BY m.member_id
```

Join once and use a conditional sum:

```
SELECT m.member_id, name, 
    SUM(CASE WHEN s.type = 1 then s.amount ELSE NULL END) as savings1,
    SUM(CASE WHEN s.type = 2 then s.amount ELSE NULL END) as savings2,
    ...
LEFT JOIN savings s s2 ON s.member_id = m.member_id AND s.paid = 1
WHERE active = 1
GROUP BY m.member_id
```

Multiple Left Join on same table

Solution

Problem