mysql nested query

[abhijeet]

i have the \'category\' table in the following format

 

-------------------------------------------

id categoryname maincatid

-------------------------------------------

 

1 A 0

 

2 B 0

 

3 C 0

 

4 D 1

 

5 E 4

 

6 F 5

-------------------------------------------

 

As you can see A,B and C are the parent categorie. D is the subcategory of A, E is again subcategory of D and F is subcategory of E.

 

So the tree structure is like A-->D-->E-->F

 

Can i write a single query in which i will specify id for F. i.e 6 and it should fetch me all parent categories upto the topmost i.e. A

[sirmanson]

I don\'t believe this is possible in one query. I scoured the mySQL page and couldn\'t find anything about this. I know it is definately possible using oracle, but not currently in mySQL. You could easily parse this recordset into PHP and get what you need.