Select in select to compute a new field

Question

Suppose a Firebird (v.2.5) Table name TREE has two columns NODE_ID and PARENT_NODE. I would like to make a query to get records with NODE_ID = 1 and calculate a new field CHILD_COUNT.

In that query, CHILD_COUNT must contain the number of records (in the same table) where PARENT_NODE = NODE_ID.

My start query is:

SELECT NODE_ID, PARENT_NODE, (select count(*) from TREE where PARENT_NODE = NODE_ID) as CHILD_COUNT
FROM TREE
WHERE PARENT_NODE = 1;

With that query, CHILD_COUNT is always 0. How can I do this query to get CHILD_COUNT process for all TREE table rows?

Answer 1

The problem is the condition in the subquery:

where parent_node = node_id

This searches for rows where both values are equal, which is not what you want: you want rows whose parent is equal to the node in the outer query. For this, you need to correlate the subquery with the outer query. So, prefix the columns with the alias of the table they belong to:

select node_id, parent_node, 
    (select count(*) from tree t1 where t1.parent_node = t.node_id) as child_count
from tree t                           -- ^ --         -- ^ --
where parent_node = 1;