Google Sheets - Query within query

Question

I've got 3 major sheets:

Group Permission: Database of permissions and levels
Profile Config: Profile settings of which groups and levels which profile will inherit
Result: A database query to show only what would be the result if the profile displayed on B1 is chosen

I was trying to query from database all rows that match group, permissions and level specified on Profile Config.

The thing is: if the profile is granted permission level 2 it will grab level 1's as well, and so forth.

https://docs.google.com/spreadsheets/d/1u4umE7qKxHOtQ8KfbafuSPHt25rTtzas/edit?usp=sharing&ouid=115849945160804839689&rtpof=true&sd=true

The formula on Result!A3 I came with was:

=QUERY('Group Permission'!A$2:G,"select A,C,F,G,D,E where (F like in ('"&QUERY('Profile Config'!A$2:D;"select C where (C IS NOT NULL) and (A like '"&B1&"')";0)&"') and(G <= '"&'Profile Config'!B$2:D&"')";0)

Answer 1

Here's one possible solution you can try out:

=let(data_,filter({'Profile Config'!C:D},'Profile Config'!A:A=B1),Λ,'Group Permission'!A:G,grp_,index(data_,,1),lvl_,index(data_,,2),
     Σ,reduce(wraprows(,6,),sequence(counta(grp_)),lambda(a,c,{a;sort(ifna(filter(choosecols(Λ,1,3,6,7,4,5),choosecols(Λ,6)=index(grp_,c),--choosecols(Λ,7)<=--index(lvl_,c)),wraprows(,6,)),4,)})),
     filter(Σ,index(Σ,,1)<>""))