By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
/
1 rows affected
SELECT *
FROM users;
| USR_TYPE | USR |
|---|---|
| Teacher | AAAA |
| Principal | AAAA |
| Employee | AAAA |
| Admin | BBBB |
| Cashier | CCCC |
| Teacher | DDDD |
| Principal | DDDD |
| Employee | DDDD |
| Admin | EEEE |
| Cashier | EEEE |
| Admin | PPPP |
| Employee | PPPP |
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'); -- there are no users in group4
INSERT INTO groups(usr_type,group_no) VALUES ('Cashier', 'Group4'); -- there are no users in group4
INSERT INTO groups(usr_type,group_no) VALUES ('Astronaut', 'Group4'); -- there are no users in group4
END;
/
1 rows affected
SELECT *
FROM groups;
| USR_TYPE | GROUP_NO |
|---|---|
| Teacher | Group1 |
| Principal | Group1 |
| Employee | Group1 |
| Admin | Group2 |
| Admin | Group3 |
| Cashier | Group3 |
| Admin | Group4 |
| Cashier | Group4 |
| Astronaut | Group4 |
-- join of users and groups for user 'EEEE':
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 |
-- group by the group_no and count how many rows are in each group
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 |
-- add the condition that the count must be the same as the row count in the groups table
-- so if there are 3 rows in the groups table, then there also be a count of 3 for the join
-- this eliminates group4, because there are 3 of those in the groups table, but only 2 in the join
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 |
-- add the conditaion that the count must be the same as the row count in the users table
-- since there are 2 rows of 'EEEE' un the users, not only the 1 found in previous SQL, 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 |
-- remove the where condition of usr = 'EEEE' to get 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 |