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 |
+-----------+-------------+
+-----------+---------+-------------+
| NIM | subtot | TA2 |
+-----------+---------+-------------+
| 107032014 | 6550000 | A_2010/2011 |
| 107032014 | 6550000 | B_2010/2011 |
| 107032014 | 6550000 | A_2011/2012 |
+-----------+---------+-------------+
+-----------+-------------+-------------+
| 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 |
+-----------+-------------+-------------+
select *,(select subtot from tableB where NIM='107032014') as subtot from tableA where NIM='107032014';
ERROR 1242 (21000): Subquery returns more than 1 row
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';