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
– BobbyA, Commented May 12, 2017 at 15:56
Here's a question with example recursive queries in SQLite: stackoverflow.com/questions/7456957/… — user1919238
– user1919238, Commented May 12, 2017 at 16:03

JNevill · Accepted Answer · 2017-05-12 16:59:33Z

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, 2017 at 16:59

answered May 12, 2017 at 16:04

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

CL. Over a year ago

Without the depth, there would be no danger of endless loops.

JNevill Over a year ago

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?

CL. Over a year ago

UNION removes any duplicate rows. (If that is not needed, UNION ALL would be more efficient.)

tom_tom Over a year ago

please see my question again. i have added an image. thanks

JNevill Over a year ago

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

|

Collectives™ on Stack Overflow

recursive sql search

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related