CODEWITHSUNDEEP
mysqlsqldatabaseoracle-database
Tori
Tori

Reputation: 15

Database/SQL Edge Cases

Okay so I'm stuck. Apparently, I'm missing edge cases... if the applicant has more skills other than the skill in skills table, it won't return that person. Here is the exact situation and the current solution I have. Please help me understand what I'm missing or where I'm going wrong because I can't figure it out because I feel like what I have is correct.

SITUATION:

Assume there are two tables: Applicant(name, skill) and Skills(skill) One applicant may have multiple skills. Make a SQL query to report: Who has all skills listed in the Skills table Just submit the SQL statement

MY ATTEMPTED SOLUTION:

SELECT name
FROM applicant
HAVING Count(skill) = (SELECT Count(skill) FROM skills)
GROUP BY(name);

Upvotes: 1

Views: 2320

Answers (2)

user5683823
user5683823

Reputation:

So, the problem is that an applicant may have skills that are not listed in the SKILLS table. That's pretty bad; skills in the SKILLS table should be primary key, and skills in the APPLICANT table should be constrained - foreign key - so they CAN'T be anything BUT what exists in the SKILLS table.

With the situation as it is, besides the syntax error Gordon has already fixed, there are TWO other, logical, issues (besides duplicate skills, too, which Gordon pointed out). An applicant may have more skills than are listed in the SKILLS table. They may also have an EQUAL number of skills as in that table, but they may miss one or two - and instead have one or two UNLISTED skills.

So, before you count, you need to restrict the skills from the APPLICANT table to those that exist in the SKILLS table.

select   name
from     applicant
where    skill in (select skill from skills)
group by name
having   count(distinct skill) = (select count(skill) from skills)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You are pretty close:

SELECT name
FROM applicant
GROUP BY name
HAVING Count(skill) = (SELECT Count(skill) FROM skills);

If there can be duplicates, you would do:

SELECT name
FROM applicant
GROUP BY name
HAVING Count(DISTINCT skill) = (SELECT Count(skill) FROM skills);

Upvotes: 1

Related Questions