COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query I'd like to do the following in one query using MySQL:


grab a row that has a parent_id of 0
grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0


How can I accomplish this in one query? Please let me know if you need more information, I will gladly be as assistive as I can. I'm not an expert on creating questions so please tell me what more information you need.

Here's an example of what I'm doing now:

select id from messages where parent_id=0


and then

select count(id) from messages where parent_id={{previously_chosen_id}}


How do I get a one shot query? Something like...

select id, count(records where parent_id=the id we just asked for)


Or, is there a better way to handle this? You see, currently I have to run a ton of queries to find the counts, when I'd rather do it in one shot.
This is easily achievable with an in-line subquery :

select  m.id,
        (select count(*) from messages where parent_id= m.id ) as ChildCount
from messages m
where m.parent_id = 0


Note that no group by is needed because a sub-query is used.