Ack, I grouped by the wrong skill column! Switch p.skill for d.skill in the inner query and all should be good.
You can't compare skills using listagg in that way. If the developer has other skills that sort between those required, it isn't returned:
create table developers (name varchar2(30), skill varchar2(30));
create table projects (name varchar2(30), skill varchar2(30));
insert into developers values ('SMITH','ORACLE');
insert into developers values ('SMITH','JAVA');
insert into developers values ('ADAMS','JAVA');
insert into developers values ('ADAMS','C++');
insert into developers values ('ADAMS','GO');
insert into developers values ('CHRIS', 'SQL');
insert into developers values ('CHRIS', 'ORACLE');
insert into projects values ('MISSION MARS','JAVA');
insert into projects values ('MISSION MARS','C++');
insert into projects values ('MISSION IMPOSSIBLE','JAVA');
insert into projects values ('MISSION SQL','SQL');
insert into projects values ('MISSION SQL','ORACLE');
select pname, dname, sum(sm), min(ct) from (
select d.name dname, p.name pname, d.skill,
count(*) ct,
sum(case when p.skill = d.skill then 1 else 0 end) sm
from developers d
cross join projects p
group by d.name, p.name, d.skill
)
group by pname, dname
having sum(sm) = min(ct);
PNAME DNAME SUM(SM) MIN(CT)
MISSION SQL CHRIS 2 2
MISSION MARS ADAMS 2 2
MISSION IMPOSSIBLE SMITH 1 1
MISSION IMPOSSIBLE ADAMS 1 1
select project, required, developer, skills, case when instr(skills,required) > 0 then 'Admitted' end as Result
from (
select name as project
, LISTAGG(skill, ';') WITHIN GROUP (ORDER BY skill) as required
from projects
group by name ) a
cross join
(select name as developer , LISTAGG(skill, ';') WITHIN GROUP (ORDER BY skill) as skills
from developers
group by name) b
where instr(skills,required) > 0 ;
PROJECT REQUIRED DEVELOPER SKILLS RESULT
MISSION IMPOSSIBLE JAVA ADAMS C++;GO;JAVA Admitted
MISSION IMPOSSIBLE JAVA SMITH JAVA;ORACLE Admitted
MISSION SQL ORACLE;SQL CHRIS ORACLE;SQL Admitted