7

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.

5

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.

3

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.

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`
  • How do you count child rows that comply with some condition? subquery? – Enrique Nov 21 '18 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.