mysql self-refrencing table returning id of parent and count of children

Question

I have a self referencing table and I want a mysql query that will return only the top most parent (parent = 0) and the number of children belonging to each of those. This is what I have so far, but I know it will not work.

SELECT id, (SELECT COUNT(id) FROM example where parent_id = id) FROM example WHERE parent_id = 0;

+--------+-----------+
|   id   | parent_id |
+--------+-----------+
|    1   |     0     |
|    2   |     1     |
|    3   |     1     |
|    4   |     0     |
|    5   |     4     |
+--------+-----------+

Hope this one help:: http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries — Sashi Kant, Feb 02 '13 at 09:23

Dave Morris · Accepted Answer · 2011-05-03T13:41:41.273

2

SELECT parent_id parent, count( * ) num_children
FROM example
GROUP BY parent_id
HAVING parent_id
IN (

SELECT id
FROM `example`
WHERE parent_id =0
)

edited May 03 '11 at 13:41

answered May 03 '11 at 13:32

Dave Morris

569
1
8
17

I changed `HAVING parent_id` to `HAVING parent_id <> 0` and now it works. – Brook Julias May 03 '11 at 16:52
1

Hmm...seems strange... worked for me perfectly. In my opinion `HAVING parent_id <> 0` is not needed here since it is already fetching the top level parents within the Subquery. – Dave Morris May 04 '11 at 05:50
I don't know what to tell you. Using the query you gave me returned an empty result, and using it with the change returned what I had wanted. – Brook Julias May 04 '11 at 12:09

Tomgrohl · Answer 2 · 2011-05-03T13:44:43.623

0

Something as simple as this should work:

SELECT parent_id, count( * ) cnt
FROM example
WHERE parent_id
IN (
   SELECT id
   FROM `example`
   WHERE parent_id =0
)
GROUP BY parent_id

edited May 03 '11 at 13:44

answered May 03 '11 at 13:30

Tomgrohl

1,767
10
16

Wouldn't this only provide me with the count of the `id`'s where the `parent_id` is equal to 0? – Brook Julias May 03 '11 at 13:33
Yes sorry, misread your question. Think @Dave Morris has got it spot on anyway. – Tomgrohl May 03 '11 at 13:42
Edited mine, but does the same thing as @Dave Morris's answer. – Tomgrohl May 03 '11 at 13:45
You're code didn't work, but I was able to give it a minor tweak and now it's working here is the code. `SELECT parent_id, count( * ) cnt FROM form_part WHERE parent_id <> 0 IN ( SELECT id FROM form_part WHERE parent_id =0 ) GROUP BY parent_id` – Brook Julias May 03 '11 at 16:50

score 0 · Answer 3 · answered May 03 '11 at 16:48

Thanks to the answers provided by Dave Morris and Tomgrohl I was able to get it to work. Here is the MySQL I used.

SELECT parent_id parent, count( * ) num_children
FROM example
GROUP BY parent_id
HAVING parent_id <> 0
IN (
    SELECT id
    FROM `example`
    WHERE parent_id = 0
);

score -1 · Answer 4 · answered May 03 '11 at 13:23

-1

You'll have to prefix columns with table alias

SELECT id, (SELECT COUNT(inner.id) FROM example inner where inner.parent_id = outer.id) FROM example outer WHERE parent_id = 0;

answered May 03 '11 at 13:23

bpgergo

15,669
5
44
68

mysql self-refrencing table returning id of parent and count of children

4 Answers4