MySql query (joins)

I have table "item" with columns::

|id(primary_key)|parent_id|name|
开发者_Python百科

I need to get all items that not have children.

SELECT [some cols]
  FROM item i
       LEFT JOIN item children ON children.parent_id = i.id
 WHERE children.id IS NULL;

SELECT * FROM item WHERE id NOT IN (SELECT parent_id FROM item)

There are other ways to write this query that may be more performant. But this should get you started.

It will select all records with an id that is not found in the parent_id column - meaning this record does not have children.