How to specify the parent query field from within a subquery in MySQL?

Question

How do I specify the parent query field from within a subquery in MySQL?

For Example:
I have written a basic Bulletin Board type program in PHP.

In the database each post contains: id(PK) and parent_id(the id of the parent post). If the post is itself a parent, then its parent_id is set to 0.

I am trying to write a mySQL query that will find every parent post and the number of children that the parent has.

$query = "SELECT id, (       SELECT COUNT(1)        FROM post_table        WHERE parent_id = id ) as num_children FROM post_table WHERE parent_id = 0"; 

The tricky part is that the first id doesn't know that it should be referring to the second id that is outside of the subquery. I know that I can do SELECT id AS id_tmp and then refer to it inside the subquery, but then if I want to also return the id and keep "id" as the column name, then I'd have to do a query that returns me 2 columns with the same data (which seems messy to me)

$query = "SELECT id, id AS id_tmp,              (SELECT COUNT(1)             FROM post_table             WHERE parent_id = id_tmp) as num_children          FROM post_table          WHERE parent_id = 0"; 

The messy way works fine, but I feel an opportunity to learn something here so I thought I'd post the question.

Answer 1

How about:

$query = "SELECT p1.id,                   (SELECT COUNT(1)                      FROM post_table p2                     WHERE p2.parent_id = p1.id) as num_children             FROM post_table p1            WHERE p1.parent_id = 0"; 

or if you put an alias on the p1.id, you might say:

$query = "SELECT p1.id as p1_id,                   (SELECT COUNT(1)                      FROM post_table p2                     WHERE p2.parent_id = p1.id) as num_children             FROM post_table p1            WHERE p1.parent_id = 0";