August 6th, 2013, 02:43 PM
CFQuery and Recursion
Hello, I need to know how to best format a query for this and I think it will require some sort of recursion. The table is comprised of a unique ID, and then two 'interface' fields that indicate a connection between two devices.
What I need to do is, given an interface, find all the corresponding connections.
So for example if I pass in interface '8'. Say that 8 is connected to 4, and it's also connected to 9. I can get that information easily. But then I'd also need to see what else 4 is connected to and what else 9 is connected to, and so on and so on until the end of the chain of devices.
Can anyone gives me an idea of the best way to accomplish this? Thank you in advance!
August 6th, 2013, 03:41 PM
August 6th, 2013, 03:49 PM
Thank you. I'm using a MySQL database in this instance. A quick search is telling me that MySQL does not support this. Am I out of luck?
August 6th, 2013, 03:52 PM
There are ways to allow hierarchical queries through standard tables and queries, but it requires additional work and the creation of special tables to map out the hierarchies. So it's possible, but it's a lot more complicated. http://en.wikipedia.org/wiki/Nested_set_model
August 7th, 2013, 08:31 AM
Any examples on how to do this?
August 7th, 2013, 09:58 AM
If you google "SQL nested set" you'll find many results on the subject. You might start with something like this.