gyaani_guy gyaani_guy - 5 months ago 8
SQL Question

adjacency model , given an id return the leaf nodes

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

Answer

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.

Comments