How do I select a value whose id don't exist as parent to others, in mysql?

Question

Two questions:

My table has three fields: id, parent_id, name. Initially id is 0, for an element that is a child to the current element, the parent_id is set to the id of the current element....basically, infinite nesting concept. Now I need to find those elements which do not have any children or sub-elements. Any ideas?
What is the best choice to retrieve the id(auto_increment) of a record I just inserted? mysql_insert_id or last_insert_id, or something else? I need to insert two records simultaneously and use the id(primary key) from the first record as foreign key of second record.

Dan Grossman · Answer 1 · 2011-07-29T08:04:12.677

0

1) Not sure I understand your schema and how parent_id works...

SELECT
  t1.id
FROM
  table t1
LEFT OUTER JOIN
  table t2
ON
  t1.id = t2.parent_id
WHERE
  t2.parent_id IS NULL

2) LAST_INSERT_ID() is the function which returns the last autoincrement generated on the connection. I don't know what mysql_insert_id was referring to... mysql_last_insert_id in PHP perhaps? That's a PHP function that wraps a call to LAST_INSERT_ID().

edited Jul 29 '11 at 08:04

answered Jul 29 '11 at 07:58

Dan Grossman

51,866
10
112
101

It is a hierarcical database model. – Oh Chin Boon Jul 29 '11 at 08:39
That much is understood, but there's more than one way to represent that hierarchy. – Dan Grossman Jul 29 '11 at 08:42
Insightful, Dan have you any reference to examples? I am also trying to implement this in one of my projects but have only come across this. – Oh Chin Boon Jul 29 '11 at 09:00

score 0 · Answer 2 · answered Jul 29 '11 at 08:02

0

use SELF JOIN

SELECT f.is,f.parent_id,f.name FROM table f
LEFT JOIN table s ON s.id = f.parent_id
WHERE s.parent_id = 0

use last_insert_id

answered Jul 29 '11 at 08:02

xkeshav

53,360
44
177
245

score -1 · Accepted Answer · answered Jul 29 '11 at 08:31

-1

I solved it by using:

SELECT * FROM `nestmenu` WHERE `id` NOT IN (SELECT `parent_id` AS `id` FROM `nestmenu`)

However, as you can tell, this query is not at all optimized, but it does get the job done. Any ideas how to optimize it?

answered Jul 29 '11 at 08:31

Bluemagica

5,000
12
47
73

Did you try the suggestions I and others have made? They do work and MySQL tends to optimize joins better than subqueries... – Dan Grossman Jul 29 '11 at 09:07
Yep I did try, and your suggetion did not give me the result I need – Bluemagica Jul 29 '11 at 09:35

How do I select a value whose id don't exist as parent to others, in mysql?

3 Answers3