hello to all mysql list subscribers , 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 ? Thanks to all please help chris __________________________________________________ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
