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 ;