I have developed tables using adjacency list model

and have the following :-

I have a problem which I think can be solved by SELF
JOIN but I can't figure out how to do it

here is table definition

CREATE TABLE first (
firstcolumn int(11) default NULL,
secondcolumn int(11) default NULL
) TYPE=MyISAM;


INSERT INTO first VALUES (0,1);
INSERT INTO first VALUES (0,2);
INSERT INTO first VALUES (0,3);
INSERT INTO first VALUES (2,4);
INSERT INTO first VALUES (2,5);
INSERT INTO first VALUES (2,6);
INSERT INTO first VALUES (5,7);
INSERT INTO first VALUES (5,8);
INSERT INTO first VALUES (5,9);

Now I use this as a table to track unlimited sub
category branches

like taking the above as example 1 , 2 , 3 are MAIN
categories

and the category subcategory tree will be

2-->4, 5, 6

5->7 , 8 , 9

Now I want to delete the following rows using one or
two sql statements

0,2

2,4
2,5
2,6
5,7
5,8
5,9

is it possible ?

I can delete upto the second level using the statement

DELETE from first where firstcolumn=2 OR
secondcolumn=2

so only the rows

0,2

2,4
2,5
2,6


gets deleted but NOT

5,7
5,8
5,9



can you please help me formulate a SQL query which
does that ?

also if the table has a next level of data like

8 , 10

8, 11

8,12

those also must be deleted...

since 10 , 11, 12 are subcategories of 8 , which in
turn is a subcategory of 5 which in turn is a
subcategory of 2 ..and 2 is the main category

so if 2 is deleted all the below TREE must be deleted
too

ok that's it for a DELETE statement

now I want to write a SELECT statement which retrieves
all sub categories , sub sub categories , sub sub sub
categories and so on ...GIVEN a main category ID

so for example based on the above table I want to
retrieve 4 ,5 ,6 , 7 ,8 ,9 IF GIVEN 2 as the main cat
id

also last but NOT the least ..for managing unlimited
sub category branches is the above database design the
best method ?


I know there is another method formulated by Joe celko called NESTED SETS

will sql query become easier ? like adding a record ? finding immediate childs etc be easier that the above adjaceny model ?

Thanks to all


chris