SQL query to get all/only Nodes assigned in nested Groups

jbrunke

For Reports and Dashboards, I would like an SQL query which would allow me to get list of all Nodes of nested Groups.

For example: If we have a Groups setup like this:

USA

  Pennsylvania

    Philadelphia

       Location 1

          Node A

          Node B

       Location 2

          Node C

          Node D

    Lancaster

      Location 3

        Node E

        Node F

Delaware

   Dover

      Location 87

         Node Z

Groups are cataloged in Container table and lists of members are in ContainerMemberSnapshots table.

If I query the ContainerMemberSnapshots table for ContainerID = 1 (Name = USA in Container tab) it will return a list of Groups contained in the USA Group only.

I'm thinking some kind of SQL recursive query would allow the query to take the Groups found and then query for their members, etc.

Any SQL gurus out there up for the challenge?