#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    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!
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    You're talking about hierarchical queries, which are supported in different ways by different RDBMS platforms: http://en.wikipedia.org/wiki/Hierarc...queries_in_SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    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?
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    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
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    Any examples on how to do this?
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    If you google "SQL nested set" you'll find many results on the subject. You might start with something like this.

IMN logo majestic logo threadwatch logo seochat tools logo