Query two tables where one of them has been transposed

Question

I have the following tables, USER and RECORDS. The USER table simply has the name of the user and the group they have access to. If a user has null as their group (such as BOB does), that means they are a super user and have access to all the groups.

USER:

username |  group
---------|--------
Bob      |  (null)
Kevin    |     1
John     |     2
Mary     |     1

I then have a RECORDS table that has a list of records:

record | field_name | value
----------------------------
1           AGE        92
1          HEIGHT      9'2    
1         __GROUP__     1 
 
2           AGE        11
2          HEIGHT      1'1    
2         __GROUP__     2  

3           AGE        68
3          HEIGHT      6'8

This is not my design but it is what I have to query on. In the RECORDS table, the group that a record belongs to is indicated by the __GROUP__ value in the field_name column. So what I am trying to do is get a list of all the records each user has access to.

So based on the user table:

BOB has access to all the records
KEVIN and MARY only have access to records who have a field_name = '__ GROUP __' and value = 1
JOHN only have access to records who have a field_name = '__ GROUP __' and value = 2

This means

BOB has access to records 1, 2, and 3.
Kevin has access to only record 1
Mary has access to only record 1
John only has access to record 2

If the GROUP is missing from a record that means only a super user ( a user with group = null) can access it.

I understand if in the RECORDS table it would have a "GROUP" column this would be easier, but unfortunately, this isn't my database and I cannot make changes to the structure.

**Sorry, I forgot to mention it is possible for a user to be in multiple groups! For instance, Mary could be in groups 1 and 2.

well, can you show us the code what you have tried so far ..? — Barbaros Özhan, Sep 16 '20 at 18:05

score 1 · Accepted Answer · answered Sep 16 '20 at 18:06

One option uses exists:

select u.*, r.*
from user u
cross join records r 
where u.grp is null or exists (
        select 1
        from records r1
        where 
            r1.ecord = r.record 
            and r1.field_name = '__GROUP__'
            and r1.value = u.grp
)

This gives you the entire records rows that each user has access to.

If you just want the list of record ids, you can use aggregation instead:

select u.userame, r.record
from users u
cross join records r
group by u.userame, u.grp, r.record
having 
    u.grp is null 
    or max(r.field_name = '__GROUP__' and r.value = u.grp) = 1

Works perfectly. Thank you! – user2924127 Sep 16 '20 at 18:26 — user2924127, Sep 16 '20 at 18:26

Query two tables where one of them has been transposed

1 Answers1