CODEWITHSUNDEEP
mysqlsql
Mawg
Mawg

Reputation: 40140

Somewhat complex MySql query

Schema diagram below. Basically, at the bottom are skills, then jobs, then candidates (ignore the companies for now). Each skill can be associated with multiple jobs, and a job can have multiple skills. Each job applies to a single candidate who can have (had) multiple (historical) jobs, each with a single company.

I just can't figure out the queries to find all candidates with skill X.

In fact, it gets complicated, because X isn't just a single skill, it can be multiple skills, with Boolean operators, such as

find all candidates with (skill="C++" and skill="UML") and NOT(skill="Python")

where the (skill="C++" and skill="UM"L) and NOT(skill="Python") part is a string which ought to contain a valid ,SQl sub-query, but I can't figure the rest of the query.

[Update] when I said "such as", I did not mean exactly that query string. I am trying to find a way handle any query string of skills. Eg skill=VB or skill=VB and skill=C or skillFreeRTOS and not skill=Windows

Btw, the schema came form the answer to this question It looks good to me, but ...

enter image description here

Upvotes: 1

Views: 267

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

find all candidates with (skill="C++" and skill="UML") and NOT(skill="Python")

I would recommend group by and having.

select j.candidate_id
from jobs j join
     skills s
     on j.job_id = s.job_id join
     skill_names sn
     on sn.skill_id = s.skill_id
group by j.candidate_id
having sum(s.skill_name = 'C++') > 0 and
       sum(s.skill_name = 'UML') > 0 and
       sum(s.skill_name = 'Python') = 0;

The caveat is that this gets the skills attached to jobs, not directly to the candidate. Perhaps that it is a requirement for your definition of skill, but it is possible that you simply have no jobs for some skills for some candidates.

Upvotes: 1

James
James

Reputation: 3015

Maybe something like this (not tested in editor)

select distinct c.candidate_id
from candidates c
where 
  exists (
    select 1
    from skill_names sn
      join skills s
        on sn.skill_id=s.skill_id
      join jobs j
        on s.job_id=j.job_id
       and j.candidate_id=c.candidate_id
    where sn.skill_name='C++'
    ) and 
  exists (
    select 1
    from skill_names sn
      join skills s
        on sn.skill_id=s.skill_id
      join jobs j
        on s.job_id=j.job_id
       and j.candidate_id=c.candidate_id
    where sn.skill_name='UML'
    ) and 
  not exists (
    select 1
    from skill_names sn
      join skills s
        on sn.skill_id=s.skill_id
      join jobs j
        on s.job_id=j.job_id
       and j.candidate_id=c.candidate_id
    where sn.skill_name='Phyton'
    )

The problem here is the skills are not related to the candidate, but the job. So the candidate could have joined the job, but it does not reveal his real skills. For a more straightforward and comprehensible model to execute that query, the skills should be directly connected with the candidate.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

could be using a subquery for skill_name IN IN ('C++', 'UML') join to skill_name NOT IN ('Python')

select c.* 
from camdidates c 
inner join jobs j on c.candate_id = j.candidate_id 
inner join (
select  sn.skill_id, sn.job_id
from  skill_name sn
inner join  skill s1 on s1.skill_id = sn.skill_id 
    and s.skill_name IN ('C++', 'UML')
inner join  skill s2 on s2.skill_id = sn.skill_id 
    and s2.skill_name NOT IN ('Python')
) t on t.job_id = j.job_id

but if you want 'C++' AND 'UML' then could be using 3 join with skill 

select c.* 
from camdidates c 
inner join jobs j on c.candate_id = j.candidate_id 
inner join (
select  sn.skill_id, sn.job_id
from  skill_name sn
inner join  skill s1 on s1.skill_id = sn.skill_id 
    and s1.skill_name = 'C++'
inner join  skill s3 on s1.skill_id = sn.skill_id 
    and s3.skill_name = 'UML'     
inner join  skill s2 on s2.skill_id = sn.skill_id 
    and s2.skill_name <> 'Python'
) t on t.job_id = j.job_id

Upvotes: 0

Related Questions