GROUP_CONCAT question

[ben jaz]

Hi.

I want to see a list of all parent links (that have parent field NULL) and a grouped list of their children. I am building a small 2 layer navigation.

My query doesn't seem to show correct results. I appreciate any help.

So far this is what my query returns:http://grab.by/zwQq

Cheers



CREATE TABLE `admin_nav` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT '',
`parent` int(11) DEFAULT NULL,
`link` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1




SELECT admin_nav.title,
admin_nav.id,
children.children,
children.parent
FROM admin_nav
LEFT OUTER JOIN (SELECT Group_concat(title) AS children,
parent
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