I need to write a query to return user_type based on all types that user was associated to in a single row

I have table with a user column and user_type column. A user can be associated to multiple user_type’s.

There are a total of five different user_type’s in the column. I have a requirement to find each user who is associated with all the user_type’s.

For example:

User_type       User
--------------------
Teacher         AAAA
Principal       AAAA
Employee        AAAA
Admin           BBBB
Cashier         CCCC
Teacher         DDDD
Principal       DDDD
Employee        DDDD
Admin           EEEE
Cashier         EEEE

Suppose this is the table (this is just a sample table). Here AAAA needs to be declared as UT_NEW1
BBBB as UT_NEW2
CCCC as UT_NEW3
DDDD again as UT_NEW1
and EEEE as UT_NEW4.

We cannot use count in this case as it cannot be determined that if a particular user has three user_type’s, and then they can be declared as a particular group.

I am trying to do:

If User = ‘Some user’ and his user_type = (‘user_type1’ and ‘user_type2’ and ‘user_type3’) then he/she can be declared as ‘type1’.

A user belongs to a group defined by particular user_types. Only if a user have those particular user_types and none other, can they be declared to belong to the group.

Answer

I added a user “PPPP” and created the table. I changed the column name to usr, since Oracle is a little picky about using keyword as columns:

CREATE TABLE users(
    usr_type VARCHAR(30),
    usr      VARCHAR(30)
);
BEGIN
  INSERT INTO users(usr_type,usr) VALUES ('Teacher',   'AAAA');
  INSERT INTO users(usr_type,usr) VALUES ('Principal', 'AAAA');
  INSERT INTO users(usr_type,usr) VALUES ('Employee',  'AAAA');
  INSERT INTO users(usr_type,usr) VALUES ('Admin',     'BBBB');
  INSERT INTO users(usr_type,usr) VALUES ('Cashier',   'CCCC'); -- CCCC doesn't belong to any group
  INSERT INTO users(usr_type,usr) VALUES ('Teacher',   'DDDD');
  INSERT INTO users(usr_type,usr) VALUES ('Principal', 'DDDD');
  INSERT INTO users(usr_type,usr) VALUES ('Employee',  'DDDD');
  INSERT INTO users(usr_type,usr) VALUES ('Admin',     'EEEE'); -- <-- this is the initial focus 
  INSERT INTO users(usr_type,usr) VALUES ('Cashier',   'EEEE'); -- <-- this is the initial focus
  INSERT INTO users(usr_type,usr) VALUES ('Admin',     'PPPP'); -- PPPP doesn't belong to any group
  INSERT INTO users(usr_type,usr) VALUES ('Employee',  'PPPP'); -- PPPP doesn't belong to any group
END;
/

Since this is a database, I decided to add a table holding the groups (you’ve also called them types):

CREATE TABLE groups(
    usr_type VARCHAR(30),
    group_no VARCHAR(30)
);
BEGIN
  INSERT INTO groups(usr_type,group_no) VALUES ('Teacher',   'Group1');
  INSERT INTO groups(usr_type,group_no) VALUES ('Principal', 'Group1');
  INSERT INTO groups(usr_type,group_no) VALUES ('Employee',  'Group1');
  INSERT INTO groups(usr_type,group_no) VALUES ('Admin',     'Group2');
  INSERT INTO groups(usr_type,group_no) VALUES ('Admin',     'Group3');
  INSERT INTO groups(usr_type,group_no) VALUES ('Cashier',   'Group3');
  INSERT INTO groups(usr_type,group_no) VALUES ('Admin',     'Group4'); -- no users in Group4
  INSERT INTO groups(usr_type,group_no) VALUES ('Cashier',   'Group4'); -- no users in Group4
  INSERT INTO groups(usr_type,group_no) VALUES ('Astronaut', 'Group4'); -- no users in Group4
END;
/

I picked user “EEEE” and made a simple join:

SELECT *
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE usr = 'EEEE';

+----------+------+----------+----------+
| USR_TYPE | USR  | USR_TYPE | GROUP_NO |
+----------+------+----------+----------+
| Admin    | EEEE | Admin    | Group2   |
+----------+------+----------+----------+
| Admin    | EEEE | Admin    | Group3   |
+----------+------+----------+----------+
| Cashier  | EEEE | Cashier  | Group3   |
+----------+------+----------+----------+
| Admin    | EEEE | Admin    | Group4   |
+----------+------+----------+----------+
| Cashier  | EEEE | Cashier  | Group4   |
+----------+------+----------+----------+

User “EEEE” belongs to Group3 only. Not Group2, since “EEEE” has both user_type “Admin” and “Cashier”, and also not Group4, since Group4 also has the additional “Astronaut”.

First, I just made a simple group by:

SELECT g.group_no, count(g.usr_type)
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE usr = 'EEEE'
 GROUP BY g.group_no;

+----------+-------------------+
| GROUP_NO | COUNT(G.USR_TYPE) |
+----------+-------------------+
| Group4   |                 2 |
+----------+-------------------+
| Group2   |                 1 |
+----------+-------------------+
| Group3   |                 2 |
+----------+-------------------+

Then I added the condition that the count has to be indentical to the count of the same group_no in the groups table, so if there are 3 rows with Group4 in the groups table, there also have to be 3 rows in the join. But since there are only 2 rows in the join, but 3 rows in the groups table for Group4, that row is now gone:

SELECT g.group_no, count(g.usr_type)
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE usr = 'EEEE'
 GROUP BY g.group_no
HAVING count(g.usr_type) = (SELECT count(1)
                              FROM groups g2
                             WHERE g.group_no = g2.group_no);

+----------+-------------------+
| GROUP_NO | COUNT(G.USR_TYPE) |
+----------+-------------------+
| Group2   |                 1 |
+----------+-------------------+
| Group3   |                 2 |
+----------+-------------------+

Then I added a similar condition for the users table. If there are 2 users in the users table, then the count in the join has to also be 2. Since there are 2 rows with “EEEE” in the users table, but only a count of 1 in the join, Group2 is now also gone:

SELECT g.group_no
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE 1 = 1  -- always true
   AND usr = 'EEEE'
 GROUP BY g.group_no, u.usr
HAVING count(g.usr_type) = (SELECT count(1)
                              FROM groups g2
                             WHERE g.group_no = g2.group_no)
   AND count(g.usr_type) = (SELECT count(1)
                              FROM users u2
                             WHERE u.usr = u2.usr);

+----------+
| GROUP_NO |
+----------+
| Group3   |
+----------+

So we end up with having ‘EEEE’ belong to group3 🙂 Note: I also removed selecting the actual count(g.usr_type), since it’s irrevant now.

Removing the query conditition for only “EEEE” gets the group for all users:

SELECT u.usr, g.group_no
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE 1 = 1  -- always true
 GROUP BY g.group_no, u.usr
HAVING count(g.usr_type) = (SELECT count(1)
                              FROM groups g2
                             WHERE g.group_no = g2.group_no)
   AND count(g.usr_type) = (SELECT count(1)
                              FROM users u2
                             WHERE u.usr = u2.usr);

+------+----------+
| USR  | GROUP_NO |
+------+----------+
| AAAA | Group1   |
+------+----------+
| BBBB | Group2   |
+------+----------+
| DDDD | Group1   |
+------+----------+
| EEEE | Group3   |
+------+----------+

It’s also available at dbfiddle.uk, if you want to play with it.