我正在尝试创建mysql查询以获得平衡 (sum(col1)-Sum(col2)) 以及另一个表中该用户ID的名称。

-------
table: user
--------
name userid
Kashif Mehmood U001
Kamran Ahmad33 U004
Junaid Akram U006
-------

table: taccbalance, 
--------
userid amountin amountout
U001  5000.00 NULL
U001 NULL 1000.00
U001 10000.00 NULL
U001 NULL 9000.00
U002 10000.00 NULL
U002 NULL 9000.00
U002 5000.00 NULL
U002 NULL 3000.00
-----------------------

获取余额的查询是

SELECT userid, SUM(amountin)-SUM(amountout) AS balance
FROM taccbalance
GROUP BY userid;

但是如果我加入它，它就不起作用了

SELECT a.name, b.userid, b.balance
FROM user AS a
INNER JOIN 
    (SELECT userid, SUM(amountin)-SUM(amountout) AS balance
     FROM taccbalance GROUP BY userid) AS b
ON a.userid = b.userid;

它只返回一行，而应该返回多行。
有人能帮忙吗？