So, I have a parent table, that has children records, who at the same time have details (3 level parenting tables). I need to get the parent names, with their respective children and children_detail COUNTs.
parent
children
children_detail
This works until the second level:
SELECT parent.id_parent, parent.name,
COUNT(pos.id_pos) AS q
FROM parent
LEFT JOIN children ON children.id_parent = parent.id_parent
GROUP BY parent.id_parent ORDER BY parent.name
How can I also get the third level count in the same SELECT? Thanks!
As your tables are not large, try using a sub-query:
SELECT parent.id_parent, parent.name,
COUNT(pos.id_pos) AS q,
(SELECT COUNT(*) FROM children_detail cd WHERE cd.id_child = children.id_child) as cdc
FROM parent
LEFT JOIN children ON children.id_parent = parent.id_parent
GROUP BY parent.id_parent ORDER BY parent.name
Sorry, the following one should also work:
SELECT parent.id_parent, parent.name,
COUNT(pos.id_pos) AS q,
COUNT(cdc.id_detail) as cdc
FROM parent
LEFT JOIN children ON children.id_parent = parent.id_parent
LEFT JOIN children_detail cdc on cdc.id_child = children.id_child
GROUP BY parent.id_parent, parent.name
ORDER BY parent.name
If all else fails, you can use two sub-queries (very bad practice but should be fine for tables of your size):
SELECT parent.id_parent, parent.name,
COUNT(pos.id_pos) AS q,
(SELECT COUNT(*) FROM children_detail cd WHERE cd.id_child IN (SELECT cc.id_child FROM children cc where cc.id_parent = c.id_parent)) as cdc
FROM parent
LEFT JOIN children c ON c.id_parent = parent.id_parent
GROUP BY parent.id_parent
ORDER BY parent.name