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.
Advertisement
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.