Sophisticated (for me) SQL query

Question

I used only simple SQL statements in the past, but suddenly found that I needed to do something more complex and went out of my depth. Any help would be appreciated.

I have tables:

• Candidates
• CandidateLanguages
• CandidateSkills

Each candidate can have more than one language and more than 1 skill

So, for Candidate "FRED" its entries in CandidateLanguages may be

 FRED - ENGLISH FRED - FRENCH 

and his entries in CandidateSkills may be

 FRED - RUNNING FRED - JUMPING 

and for Candidate "JIM" its entries in CandidateLanguages may be

 JIM - ENGLISH 

and his entries in CandidateSkills may be

 JIM - RUNNING 

In my request, it is necessary to select candidates that correspond to several skills and languages.

So, for example, in English:

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

Or in 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 trying to select multiple entries from the Language and Skills table, and I think it might take a join, but now I'm lost .......

Thank you for your input - much appreciated

Answer 1

The task you are solving is called the Relational Division .

See this article: Separated We stand: SQL Relational Division and this question for several ways to solve it: How to filter SQL results against has-many-through

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

 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 that seems easier to write, especially if there are many languages ​​and skills, is to use two views 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 need all the skills and all languages, just 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 

having condition can be expressed as

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

What am I supposed to do here?

• Listing all possible triplets at the candidate / language level
• Candidate Grouping
• if the score is (number of skills) * (number of languages), then for this candidate all combinations are present

EDIT:

If you want only 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 is that you can only count skills and languages ​​that match your criteria.

Answer 3

Not elegant, but effective.

 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 request can be written as “Give me all the candidates who have all the well-known skills that we indicated in the table, and all the well-known languages ​​that we indicated in the other table”, and not just “all candidates” with English and French, and both jump and run, "you can use one of these queries with data:

 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 

A complete code sample that can be tested in LINQPad is available here . (You may need to create an 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 describe the many, many relationships between candidates and skills, as well as between candidates and languages. We hope that your database has the necessary tables. The request will resemble the following:

 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')