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
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
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