Multiple Left Join on same table

mysql sql left-join

18,111

Solution 1

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

Solution 2

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

Solution 3

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

18,111

Author by

l1th1um

Updated on June 14, 2022

Comments

l1th1um about 2 years

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

Sadikhasan almost 10 years

I think there is some mistake for member_id=106 for saving2 actually it becomes 80000 and your query will return 160000
Charlesliam almost 10 years

the power of SUM with CASE function within really shorten the sql.
lc. almost 10 years

@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.
Sadikhasan almost 10 years

You have to add one more where condition like s.paid = 1 so get correct result.
Abhik Chakraborty almost 10 years

@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
Sadikhasan almost 10 years

When you add condition s.paid = 1 then also satisfy user requirements and corrected output