CODEWITHSUNDEEP
sqlpostgresql
Abdul Raheem Ghani
Abdul Raheem Ghani

Reputation: 336

SQL Query Issue in PostgreSQL

I have three tables for users and users access rights as below: 

create table users (id int, login varchar, organization varchar);
create table groups (id int, name varchar);
create table user_groups (gid int, uid int,
constraint fk_users_groups foreign key(gid) references groups(id),
constraint fk_users_users foreign key(uid) references users(id)
);

Lets insert some records: 

insert into groups values(1, 'Data Entry'), (2, 'Sender'), (3, 'Receiver'), 
(4, 'Reviewer'), (5, 'Checker'), (6, 'Approver'), (7, 'Nothing'), (8, 
'Everything'), (9, 'HR Systems'), (10, 'Check System'), (11, 'Final');
insert into users (1, 'Ahmad'), (2, 'Sam'), (3, 'John'), (4, 'Smith'), 
(5, 'Roy');
insert into user_groups values (1, 1), (2, 1), (3, 1), (8, 1), (9, 1), (4, 2), 
(5, 2), (10, 2), (11, 2), (6, 3), (7, 4);

Now I want to fetch the user id, login and user type as such if the user ha ("data entry", "sender", "receiver") groups, then "HR" should be printed (we do not care for other groups if he has or not for ex: here he has the other groups as well rather than we need for the "HR" user type) for him as user type, if he/she is in ("reviewer", "checker") groups then "Check" should be printed else "Any" should be printed for any other groups.

The desired output is:

User ID      Login       UserType  
   1         Ahmad         HR  
   2         Sam           Check  
   3         John          Any  
   4         Smith         Any

Upvotes: 2

Views: 72

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

In Postgres, DISTINCT ON might be the simplest approach:

SELECT DISTINCT ON (u.id) u.id AS user_id, u.login, g.userType
FROM users u JOIN
     user_groups ug
     ON u.id = ug.uid JOIN
     (SELECT g.*,
             (CASE WHEN g.name IN ('Data Entry', 'Sender', 'Receiver') THEN 'HR'
                   WHEN g.name IN ('Reviewer', 'Checker') THEN 'Check'
                   ELSE 'Any' 
              END) AS userType,
             (CASE WHEN g.name IN ('Data Entry', 'Sender', 'Receiver') THEN 1
                   WHEN g.name IN ('Reviewer', 'Checker') THEN 2
                   ELSE 3
              END) AS userType_priority
      FROM groups g
     ) g
     ON ug.gid = g.id
ORDER BY u.id, g.userType_priority;

Another alternative is CASE EXISTS:

select u.*,
       (case when exists (select 1
                          from user_groups u join
                               groups g
                               on ug.gid = g.id
                          where g.name in ('Data Entry', 'Sender', 'Receiver')
                         )
             then 'HR'
             when exists (select 1
                          from user_groups u join
                               groups g
                               on ug.gid = g.id
                          where g.name in ('Reviewer', 'Checker')
                         )
             then 'Check'
             else 'Any'
        end) as UserType
from users u;

Despite the complicated look of the query, this involves no aggregation or sorting, so it could have the best performance.

I would recommend including the UserType in the groups table. That would simplify the query considerably -- and make any applications that use UserType more consistent.

Upvotes: 0

Jasen
Jasen

Reputation: 12402

Add a column to the table groups that contains what should be printed, when you next need to add a new value to groups you will be glad you did!.

alter table groups add column usertype text; update groups set usertype = 'HR' where id between 1 and 3; update groups set usertype = 'Check' where id between 4 and 5;

SELECT DISTINCT u.id AS user_id, u.login,
  COALESCE(g.usertype,'any')
FROM users u
JOIN user_groups ug ON u.id = ug.uid
LEFT OUTER JOIN groups g ON ug.gid= g.id and g.userype is not null
ORDER BY u.id;

this will print two lines for any user who has both HR and Check and one line for everyone else.

Upvotes: 0

Andomar
Andomar

Reputation: 238086

You could use max(case( to find the top group someone is in. Here I've defined the order by prefixing the usertype with 1, 2 or 3:

select  u.id
,       login
,       substring(max(case
            when name in ('Data Entry', 'Sender', 'Receiver') THEN '3 HR'
            when name in ('Reviewer', 'Checker') THEN '2 Check'
            else '3 Any'
            end) from 3)
from    users u
left join
        user_groups ug
on      ug.uid = u.id
left join
        groups g
on      g.id = ug.gid
group by
        u.id
,       login

Example at DBFiddle.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175696

You could use:

SELECT DISTINCT u.id AS user_id, u.login,
       CASE WHEN g.name IN ('Data Entry', 'Sender', 'Receiver') THEN 'HR'
            WHEN g.name IN ('Reviewer', 'Checker') THEN 'Check'
            ELSE 'Any' 
       END AS userType
FROM users u
JOIN user_groups ug ON u.id = ug.uid
JOIN groups g ON ug.gid= g.id
ORDER BY u.id;

DBFiddle Demo

Upvotes: 0

Related Questions