I have three tables: Users, Rights and UserRights, which is mapping values from two first tables. Example:

Table ‘Users’

Table ‘Rights’

Table ‘UserRights’

I need to check whether the user has the appropriate right and I need to output this information inline as true/false with delimiter for each right. So the result should look like this:

I use Oracle Database

Now I’m using simple query with JOIN, but it’s not applicable in current situation:

SELECT login,
       LISTAGG(name, ';') within GROUP (ORDER BY login) AS UserRights
FROM
  (SELECT u.login,
          r.name
   FROM UserRights ur
   RIGHT JOIN Users u ON ur.user_id = u.id
   LEFT JOIN Rights r ON ur.right_id = r.id)
GROUP BY login;

The output of this select looks like this:

>Solution :

Here’s one option: cross join users and rights (because you need to check all combinations), and then outer join it to userrights (because you want to check whether that particular combination exists or not).

Sample data:

SQL> with
  2  users (id, login) as
  3    (select 1, 'test1' from dual union all
  4     select 2, 'test2' from dual
  5    ),
  6  rights (id, name) as
  7    (select 1, 'read'  from dual union all
  8     select 2, 'write' from dual
  9    ),
 10  userrights (user_id, right_id) as
 11    (select 1, 1 from dual union all
 12     select 2, 1 from dual union all
 13     select 2, 2 from dual
 14    )

Query:

 15  select u.login,
 16         listagg(r.name ||'='|| case when ur.user_id is not null then 'true' else 'false' end, '; ')
 17         within group (order by r.name) rights
 18  from users u cross join rights r
 19      left join userrights ur on ur.user_id = u.id and ur.right_id = r.id
 20  group by u.login;

LOGIN RIGHTS
----- ------------------------------
test1 read=true; write=false
test2 read=true; write=true

SQL>