This is the table for storing a categories/subcategories site navigation :

```
   $query = "CREATE TABLE categories (
    id int(11) NOT NULL auto_increment,
    name varchar(100) NOT NULL,
    parentid int(11) NOT NULL,
     PRIMARY KEY  (id)
)"  ;

```

And say these are the entries inside the table

```
id  name    parentid
1   animal  NULL
2   vegetable NULL
3   mineral NULL
4   doggie  1
5   kittie  1
6   horsie  1
7   gerbil  1
8   birdie  1
9   carrot  2
10  tomato  2
11  potato  2
12  celery  2
13  rutabaga    2
14  quartz  3

```

What I want is such an sql query that for a given id , all the leaf nodes are returned AND if the id of a leaf node is given then the leaf node is itself returned.

So if the id 2 is set , the rows returned are - carrot,tomato,potato,celery,rutabaga.
If the id is 9 is given , the row returned is - 9 itself

Possible?

my subcategoires won't go more than 3 levels deep.

I tried the code given [on this page](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) , but it doesn't give the leaf node , if the leaf node id is given.

Thanks

I tried a few queries..

```
    SELECT distinct t1.name FROM
categories AS t1 LEFT JOIN categories as t2
ON t1.id = t2.parent
 LEFT JOIN categories as t3
ON t2.id = t3.parent
WHERE  t1.parent = ? OR t1.id = ?

```

but I am simply not able to understand joins.

Edit: I can forgo the return leaf node, if leaf node id given part . Now I just need a query that will return all leaf nodes , given a category/subcategory node. Thanks again