SQL join and aggregate several tables

Question

I have the following tables:

users
 - userid
 - real name
 - other stuff

roles
 - roleid
 - description
 - other stuff

functions
 - functionid
 - description

screens
 - screenid
 - description

A user can have multiple roles, as controlled by the table

user_roles
 - userid
 - roleid

Each role can have either edit, view or no access to a given function as controlled by the table

role_functions
- roleid
- functionid
- status

status is 'E' if the role has edit permission on that function, 'V' if the role has view permission on that function, and there can either be no record, or one with a 'N' status if the role has no permission on that function.

Lastly a function has multiple screens, as controlled by the table

function_screens
 - functionid
 - screenid

It's kind of a confusing mess, I know, but the requirements for the roles, functions, and screens come from different business units so I can't simplify it. However, what I need is a query that I can give to the QA department and others that given a userid, they can list all the screens, and whether they have Edit, View or No access to that screen. If that user belongs to one role that gives them 'E' permission to a function that includes a screen, and another role that gives them 'V' permission to a function that includes the same screen, then their permission to that screen is 'E'.

Right now I'm accomplishing all these lookups using a bunch of Java code with Maps of Maps, but I'm wondering if there is a simpler way to do it in a SQL script.

Answer 1

Try this:

select s.screenid, s.description
, CASE MAX(CASE rf.status WHEN 'E' THEN 2 WHEN 'V' THEN 1 ELSE 0 END)
    WHEN 2 THEN 'E'
    WHEN 1 THEN 'V'
    ELSE 'N' END as status
from user_roles ur
join role_functions rf on rf.roleid = ur.roleid
join function_screens fs on fs.functionid = rf.functionid
join screens s on s.screenid = fs.screenid
where ur.userid = :theuser
group by s.screenid, s.description
order by s.screenid

The 2 nested cases convert E, V and N to 2, 1 and 0 and back so that MAX can be used to get the "highest" status.