select all rows who hasn't children

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
aigarzzz
Forum Newbie
Posts: 9
Joined: Thu Apr 06, 2006 1:43 am

select all rows who hasn't children

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

A self left join? A subquery? Magic?
jito
Forum Commoner
Posts: 85
Joined: Sat Mar 25, 2006 4:32 am
Location: india

Post by jito »

Code: Select all

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