SQL连接问题

Question

我有 2 张 table 。

tblparents
----------
parentid
husbandid
wifeid 

tblnode
-------
nodeid
personid
parentid

我想要的是一个 SQL 语句，它获取parentid、丈夫/妻子id以及tblnode中parentid相等的行数。

代码是我写的

SELECT t.parentid, t.spouseid, t.active, c.count FROM    
(SELECT parentid, wifeid spouseid from tblparents WHERE husbandid=1
UNION
SELECT parentid, husbandid spouseid from tblparents WHERE wifeid=1) t 
INNER JOIN 
(SELECT COUNT(*) count FROM tblnodes WHERE tblnodes.parentid=t.parentid) c;

它给出错误 #1054 - “where 子句”中的未知列“t.parentid”。

有什么解决办法吗？

非常感谢，

Answer 1

试试这个:

SELECT t.parentid, t.spouseid, t.active, COALESCE(c.count, 0) AS count
FROM (
    SELECT parentid, wifeid spouseid
    FROM tblparents
    WHERE husbandid = 1

    UNION

    SELECT parentid, husbandid spouseid
    FROM tblparents
    WHERE wifeid = 1
) t
LEFT JOIN (
    SELECT parentid, COUNT(*) count
    FROM tblnodes
    GROUP BY parentid
) c
    ON c.parentid = t.parentid