i have one table that consists of the following fields
id, parent_id, depth, priority, name
the parent_id relates to the id of another item in the table which it falls below. depth is how many folders deep the entry is. sort of like a file structure where every folder is a row in the table.
well, i want to write one query to pull all the entries and display them like a folder tree. its easy enough to pull the parents priority order by that then order by its own priority. but since the number of levels deep this can span is infinite, it would have to be a recursive subquery...if thats even possible?
at the time of entry for a new row, i could grab info from the parent and its grandparent etc and add it to a new column, but i'm not sure what info to grab or how to use that info. any ideas are appreciated!