hierarchical query

[Guest_imported]

Say, I have a "Thing" table that's structure as follows:

+---------+------+-...-+---------------+
| ThingID | Name | ... | ParentThingID |
+---------+------+-...-+---------------+
| | | | |

Where a Thing can be a child of another Thing and on, and on. Say, for instance a Thing named "C" is a child of "B", which is a child of "A". Is there a way to do a single query that will return "A/B/C"?

 

[fluteplr]

it is easy to do if the number of levels in the hierarchy is fixed

select t1.thingid t1id, t1.name t1name,
t2.thingid t2id, t1.name t2name,
t3.thingid t3id, t1.name t3name
from tablename t1, tablename t2, tablename t3
where
t3.parentid = t2.id and t2.parentid = t1.id

add outer joins if needed