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
like taking the above as example 1 , 2 , 3 are MAIN
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
is it possible ?
I can delete upto the second level using the statement
DELETE from first where firstcolumn=2 OR
so only the rows
gets deleted but NOT
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
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
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
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 ?