I have a table recruiter with columns company and location. I want to find all the distinct locations where there is at least one recruiter from each company in a list.
I can find all the locations where a recruiter from a given company works
SELECT DISTINCT location
FROM recruiter
WHERE company='Google'
but I want to do this for a bunch of different companies and get the intersection of them.
I found a previous question which seemed to ask something similar: Intersection of two select.
However, the question asks specifically about the intersection of the results of two SELECT queries, and the answers don’t seem to generalize to an arbitrary number.
>Solution :
A reproducible example with sample data and expected results would be quite helpful here. My guess is that you want something like
select location, count(distinct company)
from recruiter
where company in ('Google', 'Microsoft', 'Amazon')
group by location
having count(distinct company) = 3;
which would return one row for every location where there is at least one row for each of the three companies. Of course, if you add additional companies to the list, you’d need to adjust the literal in the having clause.