Das Zählen von verweisenden Zeilen in einer MySQL-Tabelle

Question

Ich habe eine Tabelle, die so aussieht:

Id | Name                    | Parent
---+-------------------------+-------
1  | Parent One              | 0
2  | Child of Parent One     | 1
3  | Parent Two              | 0
4  | Parent Three            | 0
5  | Parent Four             | 0
6  | Child of 1st Parent     | 1
7  | Child of 2nd Parent     | 3
8  | Child of 3nd Parent     | 4

Die Tabelle repräsentiert keine Hierarchie: Jedes Element ist entweder ein Kind oder ein Elternteil, aber nicht beides.

Ich möchte eine Abfrage ausführen, die dies zurückgibt:

Id | Name                    | ChildCount
---+-------------------------+-----------
1  | Parent One              | 2
3  | Parent Two              | 1
4  | Parent Three            | 1
5  | Parent Four             | 0

Ich habe vermutet, dass dies funktionieren könnte, aber das tat es nicht:

SELECT parents.id, parents.name, COUNT(parents.id = children.parent) AS childCount
FROM (SELECT * FROM items WHERE parent = 0) parents,
     (SELECT * FROM items WHERE parent > 0) children

Wie sollte ich das machen?

Answer 1

SELECT a.id, a.Name, COUNT(b.id) as ChildCount
FROM table1 a
LEFT JOIN table1 b ON (b.Parent = a.id)
GROUP BY a.id [,a.Name] // ,a.Name is not mandatory for mysql, `GROUP BY a.id` is enough

Sie möchten möglicherweise auch WHERE a.Parent = 0 hinzufügen, um nur Elternzeilen anzuzeigen.

Aktualisiert (COUNT(*) wurde zu COUNT(b.id) geändert)

Für ältestes Kind:

SELECT x.id, x.Name, x.ChildCount, c.Name AS eldest_child_name
FROM
(
SELECT a.id, a.Name, COUNT(b.id) as ChildCount, MAX(b.id) as max_child_id
FROM table1 a
LEFT JOIN table1 b ON (b.Parent = a.id)
GROUP BY a.id 
)X
LEFT JOIN table1 c ON (c.id = X.max_child_id)