Page 1 of 1

select all rows who hasn't children

Posted: Mon Aug 28, 2006 7:23 am
by aigarzzz
i have a menu table with columns:
groupId (integer), name (text), parentId(integer).
parentId points to same table groupId. if parentId=0, then it is root menu. There can be multiple root menus. example:
menu 1
_submenu1
menu2
_submenu2
_submenu3
__subsubmenu1

in db will be:
groupId | name | parentId
---------------------------------------------
1 | menu1 | 0
2 | submenu1 | 1
3 | menu2 | 0
4 | submenu2 | 3
5 | submenu3 | 3
6 |subsubmenu1 | 5

how can i select all rows in mysql5 who hasn't children, in this case: submenu1,submenu2 and subsubmenu1?

Posted: Mon Aug 28, 2006 3:27 pm
by feyd
A self left join? A subquery? Magic?

Posted: Thu Aug 31, 2006 3:23 am
by jito

Code: Select all

SELECT * FROM `tbl_menu` WHERE groupId not in(select  parentId from `tbl_menu`);