SQL statement to get all companies that have all of a list of competencies

Question

I have a table of companies and I have a table of competencies and a joining table; companies_competencies

I'm trying to get all companies where they are linked to all competencies in a certain data set.

So for example I need to get all companies that have the competencies 1,2 and 3.

I've started with this query:

SELECT companies.id, companies.name FROM companies, companies_competencies
WHERE companies.id = companies_competencies.companies_id
AND companies_competencies.competency_id IN(1,2,3)
GROUP BY companies.id

This gives me all companies that have 1 or many of the competencies, but I'm trying to get companies that specifically have all of them.

score 5 · Accepted Answer · answered Jan 24 '14 at 15:39

5

This will get you the list of company IDs having all of the necessary competencies:

select companies_id
from companies_competencies
where competency_id in (1, 2, 3)
group by companies_id
having count(distinct competency_id) = 3 --the # of unique competency IDs

You can then do:

select *
from companies
where id in (
    select companies_id
    from companies_competencies
    where competency_id in (1, 2, 3)
    group by companies_id
    having count(distinct competency_id) = 3
)

answered Jan 24 '14 at 15:39

D'Arcy Rittich

167,292
40
290
283

4

And here are 12 more ways to do it: [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) – ypercubeᵀᴹ Jan 24 '14 at 15:49
Thanks! It was the 'having count' that I was unaware of. great stuff – Michael Wilson Jan 24 '14 at 16:07

score 2 · Answer 2 · answered Jan 24 '14 at 15:39

2

Will the companies only have one of each competency? Meaning, a certain company will have only ONE instance of competency ID 1, etc?

If so, since you're already grouping, you can modify your statement like so:

SELECT companies.id, companies.name FROM companies, companies_competencies
WHERE companies.id = companies_competencies.companies_id
AND companies_competencies.competency_id IN(1,2,3)
HAVING COUNT(*) = 3
GROUP BY companies.id

That will return only companies that have exactly 3 records/competencies.

answered Jan 24 '14 at 15:39

Joel

2,227
1
17
23

Note: This will return incorrect results if a company has the same competency more than once. – D'Arcy Rittich Jan 24 '14 at 15:46
@RedFilter agreed, which is why I specifically mentioned each company having a specific competency only once. – Joel Jan 24 '14 at 15:48
I could only accept one answer but this would have worked for me too thanks Joel – Michael Wilson Jan 24 '14 at 16:08
@MichaelWilson No problem, the other answer is more robust and deserves to be the accepted. Thanks! – Joel Jan 24 '14 at 16:10

SQL statement to get all companies that have all of a list of competencies

2 Answers2