TSQL: Relational Division with Remainder (RDWR)-CodePudding

I have mapping table CandidatesSkills which holds the mapping between candidate and the skills they possess. Then I have another table JobRequirements that maps jobs and required skills for that jobs.
A candidate can apply to a job if he possesses ALL the required skills for that job. A candidate can have extra skills. Given CandiateID I want to find all the jobs that candidate can apply.

I think this is Relational Division with Remainder in SQL. And there is an

Job to required skills mapping

based on the dataset, the query below should return JobID 2,3 and 5

Here my SQL (based on Peter Larsson (PESO) Solution for RDNR/RDWR)

DECLARE @CandidateID INT = 1

SELECT JobID 
FROM        
(
    SELECT jr.JobID
        ,cnt=SUM(CASE WHEN jr.SkillID = c.SkillID THEN 1 ELSE 0 END)
        ,Items=COUNT(*)
    FROM dbo.JobRequirements AS jr
    CROSS JOIN dbo.CandidatesSkills  AS c    
    WHERE c.CandidateID = @CandidateID
    GROUP BY jr.JobID, jr.SkillID
) d
GROUP BY JobID
HAVING  SUM(cnt) = MIN(Items)
    AND MIN(cnt) >= 0;

However, query does not return anything. Trying to find what's wrong with my query

Here is the SQL Fiddle

CodePudding user response：

Something like:

DECLARE @CandidateID INT = 1;

with cj as
(
  select cs.CandidateId, 
         jr.JobId, 
         count(*) over (partition by jr.JobId, cs.CandidateId) skillsPosessed,
         (select count(*) from JobRequirements where JobId = jr.JobId) skillsRequired
  from CandidatesSkills cs
  join JobRequirements jr
    on cs.SkillId = jr.SkillId
)
select distinct cj.CandidateId, cj.JobId
from cj
where cj.skillsPosessed = cj.skillsRequired

CodePudding user response：

In this case, you doing relational division with multiple divisors. In other words, you are dividing each set of JobRequirements per each JobID, by the CandidateSkills of that candidate.

In this case, a LEFT JOIN solution is much simpler

DECLARE @CandidateID INT = 1;

SELECT jr.JobID
    ,Skills = COUNT(c.SkillID)
    ,Requirements = COUNT(*)
FROM dbo.JobRequirements AS jr
LEFT JOIN dbo.CandidatesSkills  AS c ON c.SkillID = jr.SkillID
    AND c.CandidateID = @CandidateID
GROUP BY jr.JobID
HAVING COUNT(*) = COUNT(c.SkillID);

What this does is left-join the candidate's skills to the requirements. We then simply count up all the Requirements for the JobID, and ensure it is equal to the number of matches.

Another way to write this is

HAVING COUNT(CASE WHEN c.SkillID IS NULL THEN 1 END) = 0;

In other words: the number of non-matches should be zero.

SQL Fiddle