SQL Join Problem

Question

I have 2 tables.

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

tblnode
-------
nodeid
personid
parentid

What i want is an SQL statement that gets parentid, husband/wifeid and the count of rows in tblnode where parentid's are equal.

I wrote the code

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;

It gives an error #1054 - Unknown column 't.parentid' in 'where clause'.

Any ideas how to solve ?

Thanks a bunch,

Answer 1

There was a syntax error. Try this:

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 parentid, COUNT(*) count
 FROM tblnodes GROUP BY parentid) c
ON c.parentid=t.parentid) c ;

However the following query will perform better:

SELECT t.parentid, t.wifeid spouseid, t.active, COUNT(t.parentid) CNT
FROM tblparents t LEFT JOIN tblnode c
    ON t.parentid = c.parentid
GROUP BY t.parentid. t.wifeid, t.active