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

Question

Is there a way to 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

Give the tables unique names:

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

Answer 2

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";

Answer 3

You could try something like this

SELECT  pt.id,
        CountTable.Cnt
FROM    post_table pt LEFT JOIN
        (
            SELECT  parent_id,
                    COUNT(1) Cnt
            FROM    post_table
            WHERE   parent_id <> 0
            GROUP BY parent_id
        ) CountTable ON pt.id = CountTable.parent_id
WHERE   pt.parent_id = 0

To get back to your example, use the alias of the main table in the sub select

SELECT  pt.id,
        (SELECT COUNT(1) FROM post_table WHERE parent_id = pt.id) 
FROM    post_table pt
WHERE   pt.parent_id = 0

Answer 4

The following syntax works in Oracle. Can you test if the same works in MYSQL too? It is called scalar subquery in Oracle.

You would just need to alias the two tables differently to distinguish between them if you are using the same table twice.

sql> select empno,
  2         (select dname from dept where deptno = emp.deptno) dname
  3    from emp 
  4    where empno = 7369;

     EMPNO DNAME
---------- --------------
      7369 RESEARCH

sql> select parent.empno,
  2         (select mgr from emp where empno = parent.empno) mgr
  3    from emp parent
  4    where empno = 7876;

     EMPNO        MGR
---------- ----------
      7876       7788