CODEWITHSUNDEEP
sqlpostgresql
sjain
sjain

Reputation: 23354

override result of 2nd table with 1st table when boolean is TRUE in 2nd table

       role_cap                                    enduser_cap
       _____________________________________      _____________
       role_id  user_cap_id  is_present           id  user_cap_id is_granted
       -------------------------------------      -------------------------
       8        29            false               17    50         true
       7        32            true                21    30         true
       8        30            false               66    20         false
       8        13            true                21    29         true
       8        11            false

I want all the user_cap_id of role_cap table which have role_id = 8 whose is_present is overridden by is_granted of enduser_cap having id = 21.

The result should become:

_________________________      
user_cap_id  
-------------------------
29
30
13

I can get it with individual queries like:

select * from role_cap where role_id = 8
select * from enduser_cap where id = 21

The result of 2nd query should be merged into 1st query such that: "override result of 2nd table with 1st table when boolean is TRUE in 2nd table"

Whatever is TRUE in role_cap and enduser_cap tables should become part of the result. However, if role_cap has FALSE entry and enduser_cap has TRUE entry for the same user_cap_id then TRUE of enduser_cap will be given as priority.

Upvotes: 0

Views: 54

Answers (2)

Ossin Java guy
Ossin Java guy

Reputation: 365

If you want to see records from role_cap (with role_id=8) that are overridden by enduser_cap records (with id=21), than use:

SELECT r.user_cap_id 
FROM role_cap r, enduser_cap e
WHERE r.role_id = 8 AND e.id=21 AND 
      r.user_cap_id=e.user_cap_id AND r.is_present <> e.is_granted

You are expected to get:

user_cap_id
------------
29
30

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

Sounds like an OR operation between checking if the user has true in enduser_cap table with id = 21 or has true in role_cap itself.

select user_cap_id
from role_cap r
where role_id = 8
    and (
        exists (
            select 1
            from enduser_cap u
            where u.user_cap_id = r.user_cap_id
                and id = 21
                and is_granted
            )
        or is_present
        );

Demo

Upvotes: 1

Related Questions