Single Table Tree Structure Database - Recursive Search
Started with a Single Table Tree Structure Database.
Every record has a unique ID and a Parent ID.
The Parent ID always matches some other records unique ID
A record can have multiple other records that record it as parent.
A record can only have a single parent.
The root record has a Parent ID that matches it's own unique ID (or zero if you like)
Given any records ID I need to be able to search up through the tree to find the first parent record or parents parent record etc that has a characteristic field = "Diamond". If not found I need to be able to return the root record.
Given the same ID I need to be able to provide a list of all the children, childrens children etc of the given record ordered by name or ID.
These two functions would be used to display for any given record...
1. Its "diamond" parent and
2. A list of it's children.
Both functions would seem to require recursion but this is a little beyond me.