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

Answer 2

Try this:

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

Answer 3

If I understand your question correctly:

SELECT t.parentid, t.husbandid, t.wifeid, COUNT(*)
LEFT JOIN tblnode n
    ON n.parentid = t.parentid
GROUP BY t.parentid. t.husbandid, t.wifeid

EDIT: This will return a minimum count of 1 though.

Answer 4

I think the table alias "t" is not being recognized in the final statement. You need to assign an alias to set returned by the unioned statements and refer to that alias in the the select count(*) statement.