Complicated SQL Statement: select that matches on multiple tables

Question

I have the tables:

• Candidates
• CandidateLanguages
• CandidateSkills

Each candidate may have more than 1 language and more than 1 skill

So for Candidate "FRED", his records in CandidateLanguages may be

FRED - ENGLISH
FRED - FRENCH

and his records in CandidateSkills may be

FRED - RUNNING
FRED - JUMPING

and for Candidate "JIM" his records in CandidateLanguages may be

JIM - ENGLISH

and his records in CandidateSkills may be

JIM - RUNNING

My query needs to select candidates that match multiple skills and languages.

So for example in English:

Select all of the candidates who speak ALL of the selected languages and have ALL of the selected skills...

Or put another way....

SELECT ALL candidates WHERE 
(language = 'FRENCH' AND language is 'ENGLISH') AND 
(skill = 'RUNNING' AND skill = 'JUMPING') 

Of the two candidates above, this should only return "FRED"

I understand that the problem is with trying to select multiple records from the Language and Skills table and I think that joins may be required, but now I am lost...

Answer 1

The problem you are solving is called Relational Division.

See this article: Divided We Stand: The SQL of Relational Division and this question for a few ways to solve it: How to filter SQL results in a has-many-through relation

One way to solve it (which will be - in general - the most efficient):

SELECT ALL c.candidate
FROM Candidates c
  JOIN CandidateLanguages lang1
    ON  lang1.candidate = c.candidate 
    AND lang1.language = 'English'
  JOIN CandidateLanguages lang2
    ON  lang2.candidate = c.candidate 
    AND lang2.language = 'French'
  JOIN CandidateSkills sk1
    ON  sk1.candidate = candidate 
    AND sk1.skill = 'Running'
  JOIN CandidateSkills sk2
    ON  sk2.candidate = candidate 
    AND sk2.skill = 'Jumping' ;

Another way, which seems easier to write, especially if there are a lot of languages and skills involved, is to use two derived tables with GROUP BY in each of them:

SELECT ALL c.candidate
FROM Candidates c
  JOIN 
    ( SELECT candidate
      FROM CandidateLanguages
      WHERE language IN ('English', 'French')
      GROUP BY candidate
      HAVING COUNT(*) = 2                     -- the number of languages
    ) AS lang
      ON  lang.candidate = c.candidate 
  JOIN 
    ( SELECT candidate
      FROM CandidateSkills 
      WHERE skill IN ('Running', 'Jumping')
      GROUP BY candidate
      HAVING COUNT(*) = 2                     -- the number of skills
    ) AS sk
      ON  sk.candidate = c.candidate ;

Answer 2

If you want all skills and all languages, simply counting the multiplications will be enough.

select c.id
from candidate c 
join candidateLanguage cl on c.id = cl.candidateId
join language l on cl.languageId = l.id
join candidateSkill cs on c.id = cd.candidateId
join skill s on s.id = cs.skillId
group by c.id
having count(*) = 4

The having condition can be expressed as

having count(*) = 
    (select count(*) from skill) * (select count(*) from language)

What do I do here?

• Listing all possible Candidate-Language-Skill triplets
• Grouping them by candidate
• if the count equals to (count of skills) * (count of languages) then for this candidate all combinations are present

EDIT:

If you only want a subset of languages and skills, you can filter it:

select c.id
from candidate c 
join candidateLanguage cl on c.id = cl.candidateId
join language l on cl.languageId = l.id
join candidateSkill cs on c.id = cd.candidateId
join skill s on s.id = cs.skillId
where l.name in ('English', 'French')
  and s.name in ('RUNNING', 'JUMPING')
group by c.id
having count(*) = 4

The difference here is that you can count only those skills and languages that are matching your criteria.

Answer 3

Not elegant, but efficient.

SELECT 
  *
FROM
  Candidates c
WHERE 
  (SELECT COUNT(*) 
   FROM   CandidateLanguages cl 
   WHERE  cl.candidateId = c.candidateId AND cl.language in ('FRENCH', 'ENGLISH')
  ) = 2
  AND
  (SELECT COUNT(*) 
   FROM   CandidateSkills cs 
   WHERE  cs.candidateId = c.candidateId AND cs.skill in ('RUNNING', 'JUMPING')
  ) = 2

Answer 4

If your data query can be written as "Give me all candidates that have all the known skills that we have listed in a table, and all known languages we have listed in a different table", and not just "all the candidates with english and french, and both jumping and running", you can use one of these data-driven queries:

select
    *
from
    Candidates as C
where
    (select count(*) from CandidateLanguages where CandidateName = C.Name) = (select count(*) from Languages)
and (select count(*) from CandidateSkills where CandidateName = C.Name) = (select count(*) from Skills)
go

select
    *
from
    Candidates
where
    Name not in (
        select 
            C.Name
        from
            (Candidates as C cross join Languages as L)
            left join CandidateLanguages as CL on C.Name = CL.CandidateName and L.Name = CL.LanguageName
        where
            CL.CandidateName is null
    )
and Name not in (
        select
            C.Name
        from
            (Candidates as C cross join Skills as S)
            left join CandidateSkills as CS on C.Name = CS.CandidateName and S.Name = CS.LanguageName
        where
            CS.CandidateName is null
    )
go

Full example code that can be tested in LINQPad is available here. (You may have to create the empty database)

Answer 5

 select candidate-name,resulttblskills1.sumCOLRATIOSKILLS,resulttbllanguages1.sumCOLRATIOLanguages from candidates candidates1

    join (select  count(*) as sumCOLRATIOskills
          from candidateskills skills1
          where skills1.requiredskills in ('jumping','canoeing','mostlygoofing'
          group by id
          ) as resulttblskills1 on resulttblskills1.id = candidates1.id 

    join (select  count(*) as sumCOLRATIOLANGUAGES
          from candidatelanguages languages1     
          where languages1.requiredlanguages in ('French','english','esparanto')
          group by id ) as resulttbllanguages1 on resulttbllanguages1.id = candidates1.id

    where resulttblskills1.sumCOLRATIOSKILLS > 1
          and resulttbllanguages1.sumCOLRATIOLANGUAGES > 1

Answer 6

You are describing a many to many relationship between candidates and skills and also between candidates and languages. Hopefully your database has the requisite tables. The query would resemble this:

select yourfields
from candidate c join candidateLanguage cl on c.id = cl.candidateId
join languages l on cl.languageId = l.id
join candidateSkill cs on c.id = cd.candidateId
join skill s on s.id = cs.skillId
where l.language in ('FRENCH', 'ENGLISH')
and s.skill in ('RUNNING', 'JUMPING')