edrianhadinata edrianhadinata - 10 months ago 113
SQL Question

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

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

Answer Source

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';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download