recursive sql search

Question

I am looking for a query, where I can ask for data by groups. example:

table group
-uID-|-parentID-|-someData-
0    |  0       | foo
1    |  0       | bar
2    |  1       | foobar
3    |  2       | test
4    |  2       | demo

The parent ID is pointing to the uID from the parental group.

For example: when I want group 3 I will get back "test" only. When I request group 1, the results will be: bar, foobar, test and demo. So I have to get all the rows, where the parentID matches my uID I am searching for.

Here is an image of the architecture for better understanding:

If I am looking for group 1, will get it and all the sub groups 2, 3 and 4.

Thank you.

Could you explain in more detail how you get bar, foobar, test and demo from requesting group 1? — BobbyA, May 12 '17 at 15:56
Here's a question with example recursive queries in SQLite: http://stackoverflow.com/questions/7456957/basic-recursive-query-on-sqlite3 — , May 12 '17 at 16:03

JNevill · Accepted Answer · 2017-05-12T16:59:33.890

3

In sqlite we can use a recursive CTE to pull this off.

WITH RECURSIVE reccte AS
(
    SELECT 
        uid as initialID,
        uID,
        parentID,
        someData,
        1 as depth 
    FROM table
    WHERE uID = 1 /*starting point for recursive stuff*/

    UNION ALL

    /*Recursive statement*/
    SELECT
        reccte.initialID,
        t1.uID,
        t1.parentID,
        someData,
        depth + 1
    FROM
        reccte
        INNER JOIN table as t1 ON
            recCTE.uID = t1.parentID /*joining up the parent*/
    WHERE depth < 15 /*keep from endless loops*/
)

/*Select all the someData's that resulted from the recursive lookup*/
SELECT someData FROM recCTE;

edited May 12 '17 at 16:59

answered May 12 '17 at 16:04

JNevill

46,980
4
38
63

Without the `depth`, there would be no danger of endless loops. – CL. May 12 '17 at 16:05
I'm not familiar with sqlite. Does it have some mechanism in recursive queries to prevent endless cycling in the event that OP's actual data might not be clean? – JNevill May 12 '17 at 16:06
UNION removes any duplicate rows. (If that is not needed, UNION ALL would be more efficient.) – CL. May 12 '17 at 16:59
please see my question again. i have added an image. thanks – tom_tom May 12 '17 at 17:20
@tom_tom This should give you the output you are looking for based on the hierarchy from your picture. `Child | Parent | attribute` tables are pretty common and recursive CTEs are how we generally solve these ones. – JNevill May 12 '17 at 18:29
thanks a lot, with a little adaption, it worked perfectly for me! – tom_tom May 12 '17 at 18:46

recursive sql search

1 Answers1