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?
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
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.