SQL Query Issue in PostgreSQL

Question

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

Andomar · Accepted Answer · 2018-05-06 06:45:01Z

1

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.

edited May 6, 2018 at 6:45

answered May 6, 2018 at 6:34

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2018-05-06 06:36:01Z

0

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

answered May 6, 2018 at 6:36

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

1 Comment

Lukasz Szozda Over a year ago

@Andomar It depends. With multiple groups you will get more than one row. Whereas in your solution I will get only one (MAX).

Jasen · Accepted Answer · 2018-05-06 07:08:46Z

0

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.

answered May 6, 2018 at 7:08

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-05-06 11:52:31Z

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.

Collectives™ on Stack Overflow

SQL Query Issue in PostgreSQL

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related