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

  • 2
    Create a dbfiddle.uk/?rdbms=sqlite_3.27 with some sample data and update your question with URL and the expected result Apr 13, 2022 at 13:13
  • 1
    Hi, and welcome to dba.se! in addition to what @Lennart said, round here, we like our databases defined as proper DDL - i.e. CREATE TABLE blah... and not ad-hoc descriptions of names of fields and their types. Help us to help you!
    – Vérace
    Apr 16, 2022 at 13:23

1 Answer 1


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

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

After that i querry based on the user.id 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 = f.id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.