does anybody have an easy solution to finding looped references? not sure if i've chosen the right words so i'll give an example Table:family_tree parent child 1 2 2 3 3 1 4 5 5 4 Code (markup): so this table would describe the hierarchy for people, it connects one person id to another. for simplicity we'll say that a child can only have one parent and a parent can only have one child. in the table person 1 is it's own great-grandchild and 5 is the parent of it's parent, obviously that doesn't make sense.. is there any function that could find cycles like the ones above? for any depth?
This can be done using recursive queries, not sure if possible in PL-SQL but can be done nicely in PHP.
if anyone's interested the solution i came up for with this is to query for the distinct members of the child column... i then loop through that calling a function i've made, passing it each child id one at a time.. the function uses a query something like the following the print out the family tree beneath the input_child node select child_id from table connect by child_id = parent_id start with input_child now, looping through that tree, if you come accross input_child a second time it means at some point there was a loop in the ancestry. the function returns whether or not there is a loop