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:
-
If a user has
Reviewer
orHigh Reviewer
right along with other rights, it should be counted asReviewer
. -
If a user has
Data Entry
right along with the other rights(exceptReviewer
andHigh Reviewer
), it should be counted asData Entry User
. -
If a user has
Read Only
right along with the other rights(exceptReviewer
,High Reviewer
andData Entry
), it should be counted asRead 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:
dbfiddle here