Count The number of child from tree table

Question

Hi All, 

I have a table tilted "tbltree"
Structure:
`id` int(10) unsigned NOT NULL auto_increment,
`title` longtext NOT NULL, 
`parent` int(10) unsigned default '0'

Data:

id  Title  parent
1   abc    0
2   xyz    1
3   pqr    1
4   uuu    2

Now I want the results like this

id  title  no of childs
1   abc     3
2   xyz     1
3   pqr     0
4   uuu     0

Answer 1

If you were using Oracle, I'd suggest using a CONNECT BY which would make this a simple query.

You might be able to do a group by/having as a subselect.

SELECT ID parent_id, TITLE, (
SELECT COUNT(ID) from tbltree group by ID having parent = parent_id) child_count
FROM tbltree;

This is untested, and only works for one level deep on the tree.

Answer 2

I haven't had my coffee yet, but you might want to try something along the lines of

select 
    p.id, p.title, count(c.*) 
  from tbltree as c 
  left join tbltree as p 
  where p.id = c.parent 
  group by p.id
  order by p.id

Answer 3

How about something like this?

SELECT
  a.id,
  a.title,
  COUNT(*) AS children
FROM
  tbltree AS a
  INNER JOIN tbltree AS b ON
    b.parentid=a.id
GROUP BY
  a.id

This requires only one self-join, you would want to have an index on tbltree.id for the GROUP BY statement (presumably you have it as your PRIMARY), and an index on tbltree.parentid to make this join efficient.

Answer 4

This should give you what you want EDIT It doesn't....see the last edit below:

select 
  parent.id, parent.title, count(child.id) 'number of children' 
from
  ttt parent left join ttt child
    on child.parent = parent.id
group by parent.id
order by parent.id;

Output from mysql:

+----+-------+--------------------+
| id | title | number of children |
+----+-------+--------------------+
|  1 | abc   |                  2 |
|  2 | xyz   |                  1 |
|  3 | pqr   |                  0 |
|  4 | uuu   |                  0 |
+----+-------+--------------------+

EDIT: At least in MySQL, the ORDER BY is superfluous:

If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL

Thanks for the tip, thomasrutter!

EDIT: Well, thaggie pointed out that I'm suffering from a case of not carefully reading the question here. The answer above only gets children of a parent, not all descendants. As others have pointed out, if you're using Oracle you can use their STARTS WTIH/CONNECT BY syntax. Otherwise this article may be of interest.

Answer 5

Try reading through this and seeing if it helps.