Recursive select from a single table

Databases
4

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.

1 Like