php - mysql different query

Question

I had a category table as follows

id     |    name       |  parent_of    |   created_on
-------+---------------+---------------+---------------------
1      |   Name 1      |  0            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
2      |   Name 2      |  0            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
3      |   Name 3      |  1            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
4      |   Name 4      |  1            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
5      |   Name 5      |  3            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
6      |   Name 6      |  3            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------

As in the table some category has child categories.

In this table as follows. Specifying the table id

1
  |--> 3
  |   |--> 5
  |   |--> 6
  |--> 4
2

My question is while adding a product, need to select the category for each product. But only need to show the end category. ie need not showing id 1 and 3 since it is not the end. Only need to show 5,6,4 and 2 ( those are the categories with out any category according the list )

Can any one please help me with the MySQL query for listing the category drop down ?

Thanks in advance

Answer 1

SELECT c.* FROM categories c 
LEFT JOIN categories c1 ON c.id = c1.parent_of
WHERE c1.id IS NULL

Answer 2

I hope that, this is what you want.

SELECT id FROM category where ID NOT IN (SELECT DISTINCT parent_of FROM category) ORDER BY id DESC