How to count the number of "children" in a tree?

Question

MySQL

Good day.
There is a table (the table is one, i.e. the hierarchy is tied to itself) mysql. Structure id, parent_id, title .... etc.
Is it possible to display all the fields + the number of children for each id with one (!) query?
If so, how?
Thank you.

Answer 1

Really, the decision came by itself :)

SELECT t1.id, t1.parent_id,t1.title,case when t2.cnt is null then 0 else t2.cnt end cnt FROM table t1
LEFT JOIN (SELECT parent_id, COUNT(parent_id) cnt FROM table
       GROUP BY parent_id) t2
ON t1.id = t2.parent_id

Since I asked the question, I'll post the solution. Suddenly someone will come in handy.