Recursive querry over 3 Tables

The question:

I have the following table structure

create table folder
    id   integer
        constraint folder_pk
            primary key,
    path TEXT

create index folder_path_uindex
    on folder (path);

create table membership
    id     integer
        constraint membership_pk
            primary key,
    member integer
        references user,
    groups integer
        references user

create index membership_group_member_uindex
    on membership (groups, member);

create table rule
    id        integer
        constraint rule_pk
            primary key,
    folder    integer
        references folder,
    principal integer
        references user,
    type      INTEGER,
    level     integer

create table user
    id   integer
        constraint user_pk
            primary key,
    name integer,
    type text

create index principal_name_type_uindex
    on user (name, type);

I need to walk through the groups recursively to find out if a user is efectivly a member of a group and than join against the Rule table to get all rules relevant to the user. The rules may directly apply to a user or to a group. A group can be a member of another group.

How would I start with this and is it even possible in SQLite?

I am a little bit stuck on this. Any input is appreciated


I think i have found a solution. I will write an awnser

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

I use a view to connect the user and membeship table in oder to self join them.

CREATE VIEW RecursoinView as
SELECT as UserID,  as Name, m.groups as groups , u.type as type     FROM user u join membership m on = m.member;

After that i querry based on the with the following SQL statement:

WITH    q AS 
        SELECT  *
        FROM    RecursoinView
        WHERE   RecursoinView.UserID=398 
        UNION ALL
        SELECT  m.*
        FROM    RecursoinView m
        JOIN    q
        ON      m.UserID = q.groups
FROM    q join rule on q.groups = rule.principal join folder f on rule.folder =

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment