I want to store in a single table in a database, a sort of tree structure. I have the relationships and the table all set up, but I want to be able to retrieve data in a special way.

Table Schema:
[Categories]
catID: PK
catText
superCatID: FK (Relates to catID, is not compulsory)


So, in a table, I may have the following data:

catID catText superCatID
1 General
2 Test1 1
3 Test2 1
4 Test3 2


Now, if the user wants category 4, I want to retrieve the entire chain that 4 inherits:
4 Test3 2
2 Test1 1
1 General


As you can see, catID(2) is the parent of catID(4), and catID(1) is the parent of catID(2).





Now, can this be done??