Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
feel free to leave some examples and what your query looks like right now. There are at least a half dozen ways to do this. –  jcolebrand Jul 14 '13 at 3:58
    
I added some content to my question. If you need any more, please feel free to let me know. I will be back online in the AM. –  jkushner Jul 14 '13 at 4:38
    
You want to group by parent_id and count rows in every group, selecting only results where parent_id <> 0. –  Andriy M Jul 14 '13 at 7:58
1  
Something like SELECT parent.id, COUNT(child.id) AS child_count FROM messages parent INNER JOIN messages child on child.parent_id = parent.id GROUP BY parent.id WHERE parent.parent_id = 0 will give you what you're asking for. If you edit your question to include the schema of the messages table we can give you a more precise answer. –  Nick Chammas Jul 14 '13 at 10:38
    
I will do this for you tomorrow. Please bare with me one day. –  jkushner Jul 15 '13 at 0:16

2 Answers 2

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
group by m.id
share|improve this answer
1  
group by is unnecessary, no? –  Andriy M Jul 15 '13 at 11:55
    
Yes, you're right. The grouping is redundant –  druzin Jul 17 '13 at 0:40

As I mentioned in my comment, 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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