Search code examples
sqloracle-databaseoracle12c

Intersection of many SELECT DISTINCT queries


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.