Menu
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.
510
A subquery cannot contain a BETWEEN or LIKE clause. A subquery cannot contain an ORDER BY clause. A subquery in an UPDATE statement cannot retrieve data from the same table in which data is to be updated. A subquery in a DELETE statement cannot retrieve data from the same table in which data is to be deleted.
From clause can be used to specify a sub-query expression in SQL. The relation produced by the sub-query is then used as a new relation on which the outer query is applied. Sub queries in the from clause are supported by most of the SQL implementations.
In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =.
In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.
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"; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
