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 | 1    |
|         32 |       110 |     7 | 2014 |  60000 |    4 | 1    |
|         33 |       110 |     7 | 2014 | 100000 |    5 | 1    |
|         34 |       111 |     7 | 2014 |  80000 |    2 | 1    |
|         35 |       111 |     7 | 2014 |  25000 |    3 | 1    |
|         36 |       111 |     7 | 2014 |  60000 |    4 | 1    |
|         37 |       111 |     7 | 2014 | 100000 |    5 | 1    |
|         38 |       112 |     7 | 2014 |  80000 |    2 | 1    |
|         39 |       112 |     7 | 2014 |  25000 |    3 | 1    |
|         40 |       112 |     7 | 2014 |  60000 |    4 | 1    |
|         41 |       112 |     7 | 2014 | 100000 |    5 | 1    |
|         85 |       105 |     7 | 2015 |  80000 |    2 | 1    |
|         86 |       105 |     7 | 2015 |  25000 |    3 | 1    |
|         87 |       105 |     7 | 2015 |  60000 |    4 | 1    |
|         88 |       105 |     7 | 2015 | 100000 |    5 | 1    |
|         89 |       106 |     7 | 2015 |  80000 |    2 |      |
|         90 |       106 |     7 | 2015 |  25000 |    3 |      |
|         91 |       106 |     7 | 2015 |  60000 |    4 |      |
|         92 |       106 |     7 | 2015 | 100000 |    5 |      |
|        101 |       110 |     7 | 2015 |  80000 |    2 |      |
|        102 |       110 |     7 | 2015 |  25000 |    3 |      |
|        103 |       110 |     7 | 2015 |  60000 |    4 |      |
|        104 |       110 |     7 | 2015 | 100000 |    5 |      |
|        105 |       111 |     7 | 2015 |  80000 |    2 | 1    |
|        106 |       111 |     7 | 2015 |  25000 |    3 | 1    |
|        107 |       111 |     7 | 2015 |  60000 |    4 | 1    |
|        108 |       111 |     7 | 2015 | 100000 |    5 | 1    |
|        109 |       112 |     7 | 2015 |  80000 |    2 |      |
|        110 |       112 |     7 | 2015 |  25000 |    3 |      |
|        111 |       112 |     7 | 2015 |  60000 |    4 |      |
|        144 |       110 |     7 | 2014 |  50000 |    1 | 1    |
+------------+-----------+-------+------+--------+------+------+

When member make a savings, they could choose 5 type of savings, What i want to do is to make a list of member and all of their saving.

This is mysql query

SELECT m.member_id, name, 
SUM(s1.amount) as savings1,
SUM(s2.amount) as savings2,
SUM(s3.amount) as savings3,
SUM(s4.amount) as savings4,
SUM(s5.amount) as savings5
FROM members m
LEFT JOIN savings s1 ON s1.member_id = m.member_id AND s1.type = 1 AND s1.paid = 1
LEFT JOIN savings s2 ON s2.member_id = m.member_id AND s2.type = 2 AND s2.paid = 1
LEFT JOIN savings s3 ON s3.member_id = m.member_id AND s3.type = 3 AND s3.paid = 1
LEFT JOIN savings s4 ON s4.member_id = m.member_id AND s4.type = 4 AND s4.paid = 1
LEFT JOIN savings s5 ON s5.member_id = m.member_id AND s5.type = 5 AND s5.paid = 1
WHERE 
active = 1
GROUP BY m.member_id

This is the output

+-----------+--------+----------+----------+----------+----------+----------+
| member_id | name   | savings1 | savings2 | savings3 | savings4 | savings5 |
+-----------+--------+----------+----------+----------+----------+----------+
|       105 | Andri  |     NULL |  1280000 |   400000 |   960000 |  1600000 |
|       106 | Steve  |     NULL |    80000 |    25000 |    60000 |   100000 |
|       110 | Soraya |    50000 |     NULL |    25000 |    60000 |   100000 |
|       111 | Eva    |     NULL |  1280000 |   400000 |   960000 |  1600000 |
|       112 | Sonia  |     NULL |    80000 |    25000 |    60000 |   100000 |
+-----------+--------+----------+----------+----------+----------+----------+

As you can see the calculation is not right, for example savings2 for member 105 it should be 160K. Any suggestion what should be the query for this case.

http://sqlfiddle.com/#!2/9eca9/1

Wouldn't it be simpler to do an 'GROUP BY m.member_id, s.type' then pivot around member-id? — Jacob Lambert, Jul 04 '14 at 04:39

score 10 · Accepted Answer · answered Jul 04 '14 at 04:42

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

the power of `SUM` with `CASE` function within really shorten the sql. — Charlesliam, Jul 04 '14 at 04:50
@Charlesliam Yes, and I should mention it only works because it reduces the join to a single table. If you had to sum records across multiple *tables* you couldn't do this because you'd still get a Cartesian product of rows. — lc., Jul 04 '14 at 04:52

score 5 · Answer 2 · answered Jul 04 '14 at 04:38

5

You probably do not need multiple left joins and could be done as

SELECT 
m.member_id,
m.name,
SUM(case when s.type= 1 then s.amount end) as savings1,
SUM(case when s.type= 2 then s.amount end) as savings2,
SUM(case when s.type= 3 then s.amount end) as savings3,
SUM(case when s.type= 4 then s.amount end) as savings4,
SUM(case when s.type= 5 then s.amount end) as savings5
FROM savings s
join members m on m.member_id = s.member_id
WHERE 
m.active = 1
GROUP BY m.member_id

answered Jul 04 '14 at 04:38

Abhik Chakraborty

44,654
6
52
63

I think there is some mistake for member_id=106 for saving2 actually it becomes `80000` and your query will return `160000` – Sadikhasan Jul 04 '14 at 04:46
You have to add one more where condition like `s.paid = 1` so get correct result. – Sadikhasan Jul 04 '14 at 04:57
@Sadikhasan, yes adding the condition will have only `80000` but looks like the OP is looking for `savings2 for member 105 it should be 160K` – Abhik Chakraborty Jul 04 '14 at 04:59
When you add condition `s.paid = 1` then also satisfy user requirements and corrected output – Sadikhasan Jul 04 '14 at 05:03

score 2 · Answer 3 · edited Jul 04 '14 at 04:42

this should work

SELECT m.member_id, name, 

sum((case when s1.type=1 then s1.amount end)) as savings1,

sum((case when s1.type=2 then s1.amount end)) as savings2,

sum((case when s1.type=3 then s1.amount end)) as savings3,

sum((case when s1.type=4 then s1.amount end)) as savings4,

sum((case when s1.type=5 then s1.amount end)) as savings5

FROM members m

LEFT JOIN savings s1 ON s1.member_id = m.member_id 

WHERE active = 1 and s1.paid=1

GROUP BY m.member_id

score 0 · Answer 4 · answered Jul 04 '14 at 05:14

It seems join has the problem.. U can go above answers all are good option only..

U can use Pivot Query also

SELECT m.member_id,s1.type,
SUM(s1.amount) as savings
FROM members m
LEFT JOIN savings s1 ON s1.member_id = m.member_id  AND s1.paid = 1
WHERE active = 1
GROUP BY m.member_id ,s1.type

// Pivot here

and then try for Pivoting dude.. That also would be nice...

Multiple Left Join on same table

4 Answers4

Linked