Seeing if two lists match up in SQL

SQL Server 2014.

I'm writing a stored proc.  The proc accepts a jobID as input and should return the CandidateID of all persons who qualify for the job.  There are two tables involved:

CandidateSkill
JobSkill

The only thing common between the two tables is the SkillID.  So, if I query the JobSkill table and get the following SkillID's:

SELECT SkillID from JobSkill WHERE JobID = 1278
55
82
45
23

How can I find the candidates in the CandidateSkill table who have those EXACT JobSkills?  They have to be a perfect match.

JamesNT
JamesNTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Create Proc spFindCandidateIDs
@JobID int
As
Begin

SELECT CandidateID FROM  CandidateSkill C  INNER JOIN JobSkill S 
ON C.SkillID = S.SkillID WHERE S.JobID = @JobID 

End
-- to test
Exec spFindCandidateIDs 1278 

Open in new window

0
JamesNTAuthor Commented:
Remember, if the job has skills 55,  82,  45, and 23 then the candidate must also have the skills 55, 82, 45, and 23.

JamesNT
0
Mike EghtebasDatabase and Application DeveloperCommented:
Per your description (verify):

1. We want CandidateID list from CandidateSkill:

SELECT CandidateID FROM  CandidateSkill    <-- this gives just that

The only thing common between the two tables is the SkillID:
2. CandidateSkill C  INNER JOIN JobSkill S ON C.SkillID = S.SkillID     <-- this does just that

We need to limit the search for a specific JobID :
3. WHERE S.JobID = @JobID         <-- this does just that

Do you see any problem with that? Have you tried. If so, doesn't it work for you?

Do you want also to return SkillsID?

re:> Remember, if the job has skills 55,  82,  45, and 23 then the candidate must also have the skills 55, 82, 45, and 23.

Correct, this is why we use:
CandidateSkill C  INNER JOIN JobSkill S ON C.SkillID = S.SkillID  

Mike
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

awking00Commented:
select distinct candidateid from
(select c.candidateid,
 count(j.skillid) over (partition by c.candidateid) ccnt, jcnt
 from candidateskill c,
 (select skillid,
  count(skillid) over (partition by jobid) jcnt from jobskill
  where jobid = 1278) j
 where c.skillid = j.skillid) x
where ccnt = jcnt
;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
Mike, the problem with your query is that, if a candidateid has skillid 45 and 82 but not 23 and 55, it will get returned. I believe what jamesNT wants is only those candidates with every skillid retrieved from the jobskill query based on a given jobid.
0
Mike EghtebasDatabase and Application DeveloperCommented:
@awking00,

So, he wants to show all other skills in case of a match with one skills. It will be nice to have some before and after examples. He has some data but not complete.

If what I wrote above valid, then we possibly need to use cross apply.

Thank you for the expnations,

Mike
0
JamesNTAuthor Commented:
awking00 is correct.  It's OK for a Candidate to have skills other than those required by the job, but the Candidate must have all skills required for the job to get the job.

CandidateSkill    JobSkill ---- Acceptable
34                          34
55                          55
89                          89
90
12

CandidateSkill    JobSkill ----Acceptable
34                          34
55                          55
89                          89

CandidateSkill    JobSkill ---- Unacceptable
34                          34
60                          55
89                          89      

CandidateSkill   JobSkill ---- Unacceptable
34                          34
55                          55
                              89          

JamesNT
0
JamesNTAuthor Commented:
Thank you all for your assistance.

JamesNT
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.