COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

Question

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.

Answer 1

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.

Answer 2

This query will give you the desired output:

SELECT
    parent.id
  , COUNT(child.id) AS child_count
FROM
             messages parent
  INNER JOIN messages child
    ON child.parent_id = parent.id
WHERE parent.parent_id = 0
GROUP BY parent.id;

You can see this code in action here on SQL Fiddle.

I have used a join in my solution, whereas druzin used a correlated subquery in his. Try both and see which runs faster in your environment. MySQL may reduce them to the same plan.

Answer 3

You should use LEFT JOIN instead of INNER JOIN in case category doesn't have subcategory.

     SELECT `parent`.`id`
          , `parent`.`name`
          , COUNT(`child`.`id`) AS `hasChild` 
       FROM `category` `parent` 
  LEFT JOIN `category` `child` 
         ON `parent`.`id` = `child`.`parent_id` 
      WHERE `parent`.`parent_id` = 3780 
   GROUP BY `parent`.`id`