Mysql Join two tables into one output but result Subquery returns more than 1 row

Question

I have two tables

first table is tableA:

+-----------+-------------+

| NIM       | TA          |

+-----------+-------------+

| 107032014 | A_2010/2011 |

| 107032014 | B_2010/2011 |

| 107032014 | A_2011/2012 |

| 107032014 | B_2011/2012 |

| 107032014 | A_2012/2013 |

+-----------+-------------+

and second table is tableB:

+-----------+---------+-------------+

| NIM       | subtot  |     TA2     |

+-----------+---------+-------------+

| 107032014 | 6550000 | A_2010/2011 |

| 107032014 | 6550000 | B_2010/2011 |

| 107032014 | 6550000 | A_2011/2012 |

+-----------+---------+-------------+

how do I join two tables into one output like this :

+-----------+-------------+-------------+

| NIM       | TA          | subtot      |

+-----------+-------------+-------------+

| 107032014 | A_2010/2011 | 6550000     |

| 107032014 | B_2010/2011 | 6550000     |

| 107032014 | A_2011/2012 | 6550000     |

| 107032014 | B_2011/2012 | 0           |

| 107032014 | A_2012/2013 | 0           |

+-----------+-------------+-------------+

i used select operation :

select *,(select subtot from tableB where NIM='107032014') as subtot from tableA where NIM='107032014';

but :

ERROR 1242 (21000): Subquery returns more than 1 row

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You can do what you want with a correlated subquery:

select a.*,
       (select subtot
        from tableB b
        where b.NIM = a.NIM and
              b.TA2 = a.TA
       ) as subtot
from tableA a
where a.NIM = '107032014';

Because you want 0 rather than NULL, you need a bit of extra work. Here is one method:

select a.*,
       (select coalesce(sum(subtot), 0)
        from tableB b
        where b.NIM = a.NIM and
              b.TA2 = a.TA
       ) as subtot
from tableA a
where a.NIM = '107032014';

Mysql Join two tables into one output but result Subquery returns more than 1 row

Sign up