clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799219 fiddles created (41730 in the last week).

CREATE TABLE Employees ( Employee VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE Roles ( Role VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE AppSystemCategories ( Category VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE AppSystems ( AppSystem VARCHAR(30) NOT NULL PRIMARY KEY, Category VARCHAR(30) NOT NULL REFERENCES AppSystemCategories(Category) ); CREATE TABLE EmployeeAppSystems ( Employee VARCHAR(30) NOT NULL REFERENCES Employees(Employee), AppSystem VARCHAR(30) NOT NULL REFERENCES AppSystems(AppSystem), PRIMARY KEY (Employee, AppSystem) ); CREATE TABLE RoleAppSystems ( Role VARCHAR(30) NOT NULL REFERENCES Roles(Role), AppSystem VARCHAR(30) NOT NULL REFERENCES AppSystems(AppSystem), PRIMARY KEY (Role, AppSystem) ); INSERT INTO Employees(Employee) VALUES ('bibi'),('tee'),('mimi'),('lee'), ('min'); INSERT INTO Roles(Role) VALUES ('Data Analyst'), ('Engineer'), ('Business'); INSERT INTO AppSystemCategories(Category) VALUES ('Tech'), ('Biz'); INSERT INTO AppSystems(AppSystem, Category) VALUES ('Redshift','Tech'), ('Redash','Tech'), ('Jira','Tech'), ('Tableau','Tech'), ('Figma','Tech'), ('Office','Tech'), ('MoneyForward','Biz'), ('Salesforce','Biz'), ('Notion','Biz'); INSERT INTO RoleAppSystems(Role, AppSystem) VALUES ('Data Analyst','Redshift'), ('Data Analyst','Redash'), ('Data Analyst','Tableau'), ('Data Analyst','MoneyForward'), ('Data Analyst','Notion'), ('Engineer','Figma'), ('Engineer','Jira'), ('Engineer','MoneyForward'), ('Engineer','Notion'), ('Business','Notion'), ('Business','MoneyForward'), ('Business','Office'); -- MoneyForward and Notion are used by all roles INSERT INTO EmployeeAppSystems(Employee, AppSystem) VALUES ('bibi','Redshift'), ('bibi','Redash'), ('bibi','MoneyForward'), ('bibi','Notion'), -- Partial match for Data Analyst, match for Biz systems ('tee','Redshift'), ('tee','Redash'), ('tee','Tableau'), ('tee','MoneyForward'), ('tee','Notion'), -- Perfect match for Data Analyst ('mimi','Figma'), ('mimi','Jira'), ('mimi','MoneyForward'), ('mimi','Notion'), ('mimi','Tableau'), -- Over qualified for Engineer (uses extra systems) ('lee','Notion'), ('lee','Salesforce'), -- No tech skills ('min','Redshift'), ('min','Redash'), ('min','Jira'), ('min','Tableau'), ('min','Figma'), ('min','Office'), ('min','MoneyForward'), ('min','Salesforce'), ('min','Notion'); -- min Has it all -- all candidates use notion
5 rows affected
3 rows affected
2 rows affected
9 rows affected
12 rows affected
25 rows affected
 hidden batch(es)

SELECT * FROM AppSystems; SELECT * FROM AppSystemCategories; SELECT * FROM Employees; SELECT * FROM EmployeeAppSystems; SELECT * FROM Roles; SELECT * FROM RoleAppSystems;
appsystem category
Redshift Tech
Redash Tech
Jira Tech
Tableau Tech
Figma Tech
Office Tech
MoneyForward Biz
Salesforce Biz
Notion Biz
employee appsystem
bibi Redshift
bibi Redash
bibi MoneyForward
bibi Notion
tee Redshift
tee Redash
tee Tableau
tee MoneyForward
tee Notion
mimi Figma
mimi Jira
mimi MoneyForward
mimi Notion
mimi Tableau
lee Notion
lee Salesforce
min Redshift
min Redash
min Jira
min Tableau
min Figma
min Office
min MoneyForward
min Salesforce
min Notion
Data Analyst
role appsystem
Data Analyst Redshift
Data Analyst Redash
Data Analyst Tableau
Data Analyst MoneyForward
Data Analyst Notion
Engineer Figma
Engineer Jira
Engineer MoneyForward
Engineer Notion
Business Notion
Business MoneyForward
Business Office
 hidden batch(es)

-- 集計関数を使う方法(Aggregation) -- 全てのシステムを利用する社員 select eas.Employee from EmployeeAppSystems as eas group by eas.Employee having count(*) = ( select count(*) from AppSystems group by () );
 hidden batch(es)

-- 複雑になってしまっている例 -- 全てのシステムを利用する社員 with /* 全てのシステムを取得して、全ての社員と cross join して直積結合を作ります。*/ all_app_systems as ( select * from (select distinct(AppSystem) as AllAppSystems from AppSystems) as app_systems cross join Employees ), /*「全てのシステムを利用する」とは、使わないシステムがないという事なので、 all_app_systems から使わないシステムがある社員を取得します。*/ employee_not_using_all_systems as ( select all_app_systems.Employee from all_app_systems left join EmployeeAppSystems as eas on eas.AppSystem = all_app_systems.AllAppSystems and eas.Employee = all_app_systems.Employee where eas.Employee is null group by all_app_systems.Employee ), /* 使わないシステムがある社員を`Employee`から除外すれば、 全てのシステムを利用する社員が取得できます。*/ employee_uses_all_systems as ( select e.Employee from Employees as e left join employee_not_using_all_systems as n on e.Employee = n.Employee where n.Employee is null ) select * from employee_uses_all_systems;
 hidden batch(es)