adjacency model , given an id return the leaf nodes

Question

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 , 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

You'll make your life a lot easier if you move away from an adjacency list and use either nested sets or a closure table... see [this answer](http://stackoverflow.com/a/192462/623041). — eggyal, May 09 '12 at 08:57
I agree with eggyval: as MySQL (still) does not support recursive queries, using an adjancency model is not a good fit for MySQL — , May 09 '12 at 08:59
i think you need to split tables otherwise try my code for your querry — Ankit Sharma, May 09 '12 at 10:49

gyaani_guy · Accepted Answer · 2012-05-09T12:27:36.550

So the final query that I use looks like this:

SELECT distinct t2.id , t2.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 = $id OR t1.id = $id and t2.visible = 1

if an empty result set is returned, it means an ending node was supplied and I simply return the supplied $id. its working. Hopefully it will continue to do so, because I am kind of guessing here.

Ankit Sharma · Answer 2 · 2016-07-01T13:48:05.667

0

Try this code:

select * from `categories` where id=something and parentid is not null 
union all
select * from `categories` where parentid=something;

And tell me if it works (replace something with the desired value).

edited Jul 01 '16 at 13:48

answered May 09 '12 at 10:44

Ankit Sharma

3,923
2
29
49

adjacency model , given an id return the leaf nodes

2 Answers2

Linked