Recursive select from a single table

[WolfShade]

Hello, all,

I’m trying to help a co-worker write a simple SQL query that will recursively get groups, sub-groups, and nth-level sub sub-groups from the same table. Basically, a table with (pseudo-babble ahead) a GROUPID, PARENTGROUPID, GROUPNAME, GROUPTYPE. The top-level groups all have a PARENTGROUPID of ‘0’, first-level subgroups all have a top-level GROUPID as a PARENTGROUPID, and then each level down, the PARENTGROUPID is the ID of one of the groups above that group. (Confused, yet?)

Without knowing in advance just how many sub-sub-sub-sub- to the nth degree groups there are, is there a simple (and single) query that can group this information? Or, are we stuck doing queries-within-loops-within-loops?

V/r,

[r937]

two comments:

first, your use of “parentid” signals that you’re using the adjacency model

the recursion you’re looking for is found with the nested set model

that said, a query on your model is definitely possible, but you have to give up the idea that subgroups can go to the nth degree – see http://sqllessons.com/categories.html

whatever you do, please do ~not~ run a query inside a loop

[WolfShade]

@r937

whatever you do, please do ~not~ run a query inside a loop

Queries in a loop are one of my biggest pet peeves… no worries, there, unless it is as a VERY last resort… and even then, I’d rather eat a bullet.

Thanks for the link. I will find out from the co-worker exactly how many levels down I need to go, for this.

V/r,

[RT_]

This should work (replace 6 with the id of the first group you want to select):

SELECT 
    groupId, 
    parentGroupId, 
    groupName, 
    groupType
FROM 
    Table1 
WHERE 
    groupId=6
OR
    parentGroupId=(
        SELECT 
            Table2.groupId 
        FROM 
            Table1 AS Table2 
        WHERE 
            Table2.groupId=Table1.parentGroupId
    );

I know it uses an INNER QUERY, but it does mean you should be able to get the n’th sub group, and assuming the groupId is the primary index, it should be pretty fast.