> Maybe this helps you understand better:
http://grab.by/zxwI
Most of us are understandably reluctant to click on links of unknown provenance, especially in a forum where there has been a great deal of spam. Please post the material directly in plain text.
Your query formatting misled me. The query is ...
SELECT admin_nav.title, admin_nav.id, children.children, children.parent
FROM admin_nav
LEFT JOIN (
SELECT parent, Group_concat(title) AS children
FROM admin_nav
WHERE parent IS NOT NULL
GROUP BY id
) AS children ON children.parent = admin_nav.id
WHERE admin_nav.parent IS NULL
ORDER BY admin_nav.title;
The subquery is misformed. Unlike other SQL dialects, MySQL permits Grouping By a column that is not in the Select list, and in some circumstances that can be useful, but this trick can return arbitrary and/or odd results, as this example shows.
drop table t;
create table t(a int,b int,c int);
insert into t values(1,2,3),(1,2,5),(1,3,4),(1,4,5);
select a,group_concat(c)
from t
group by b;
> I did write it in plain English
No, it's in SQL which does not deliver the desired result. Let's see it in plain English.