PostgreSQL – How to Select Specific Value from Table with Multiple Values for Primary Key

pivotpostgresql

I have three tables as follows (with sample data):

CREATE TABLE users (
  id int PRIMARY KEY,
  login varchar(20),
  organization varchar(20)
);
CREATE TABLE rights (
  id int PRIMARY KEY,
  name varchar(20)
);
CREATE TABLE user_rights_rel (
  uid int REFERENCES users,
  rid int REFERENCES rights,
  PRIMARY KEY (uid, rid)
);

-- 1-5  'A'
-- 6-10 'B'
INSERT INTO users VALUES
  (1,  'Smith',   'A'),
  (2,  'Jane',    'A'),
  (3,  'Katrin',  'A'),
  (4,  'Todd',    'A'),
  (5,  'Ervine',  'A'),
  (6,  'David',   'B'),
  (7,  'White',   'B'),
  (8,  'Anderson','B'),
  (9,  'Randi',   'B'),
  (10, 'Polak',   'B');  

INSERT INTO rights values
  (9,'Contact'),
  (62,'Download'),
  (59,'Reviewer'),
  (32,'High Reviewer'),
  (64,'Read Only'),
  (66,'Data Entry');  


INSERT INTO user_rights_rel VALUES
  (1,9),(1,64),(1,59),(1,62),
  (2,64),(2,59),(2,62),(2,66),
  (3,66),(3,62),(3,9),
  (4,66),(4,64),(4,9),
  (5,62),(5,64),(5,9),
  (6,9),(6,64),(6,62),
  (7,64),(7,62),
  (8,66),(8,9),
  (9,66),(9,64),(9,9),(9,32),
  (10,62),(10,64),(10,9);  

Now I want to write a query based on the below criteria:

  1. If a user has Reviewer or High Reviewer right along with other rights, it should be counted as Reviewer.

  2. If a user has Data Entry right along with the other rights(except Reviewer and High Reviewer), it should be counted as Data Entry User.

  3. If a user has Read Only right along with the other rights(except Reviewer, High Reviewer and Data Entry), it should be counted as Read Only User.

As per the above criteria, my desired output is:

Organization        Reviewer     Data Entry        Read Only
   A                    2            2                 1
   B                    1            1                 3  

Organization A has 2 Reviewers (Users 1 and 2). If a user has Reviewer right then we count it as Reviewer user and we ignore other rights.

Organization A has 2 Data Entry users (Users 3 and 4). If a user does not have Reviewer and High Reviewer rights then we look for Data Entry right, if it has Data Entry right then we count it as Data Entry user and ignore other rights.

Organization A has 1 Read Only user (User 5). If a user does not have Reviewer,High Reviewer and Data Entry rights, then we look for Read Only right, if it has Read Only right then we count it as Read Only user and ignore other rights.

The priority should be as follows:
1. Reviewer (if a user has it, count him/her as Reviewer and ignore other rights).
2. Data Entry (if a user has it, count him/her as Data Entry and ignore other rights).
3. Read Only (If a user has it, count him/her as Read Only and ignore other rights.)

Any help is appreciated in advance.

Best Answer

It looks like you need to define your user categories with CASE and PIVOT the result:

select organization
     , count(*) filter (where rgt=59) "Reviewer" 
     , count(*) filter (where rgt=66) "Data Entry" 
     , count(*) filter (where rgt=46) "Read Only" 
from( select users.*
           , case when exists( select *
                               from user_rights_rel 
                               where uid=id and rid in(59,32) ) then 59
                  when exists( select * 
                               from user_rights_rel 
                               where uid=id and rid=66 ) then 66
                  when exists( select *
                               from user_rights_rel 
                               where uid=id and rid=64 ) then 46 end rgt
      from users ) u 
group by organization
order by organization;
organization | Reviewer | Data Entry | Read Only
:----------- | -------: | ---------: | --------:
A            |        2 |          2 |         1
B            |        1 |          1 |         3

dbfiddle here