Intersection of many SELECT DISTINCT queries

Question

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.

Note: for N companies, there will be (N*(N-1)) / 2 possible intersections. — wildplasser, Feb 02 '22 at 12:19

score 2 · Accepted Answer · answered Feb 02 '22 at 12:19

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.

score 1 · Answer 2 · answered Feb 02 '22 at 12:30

Using multiple queries will be difficult when there is a lot of companies, you will probably need something like this

SELECT LOCATION
FROM RECRUITER
WHERE COMPANY IN (/list of companies/)
GROUP BY LOCATION
HAVING COUNT(DISTINCT COMPANY) = /count of companies/

This query will give the name of locations which has atleast one recruiter from thecompany

Intersection of many SELECT DISTINCT queries

2 Answers2