A table with col A and col B have many vs. many relationship, how do I find a distinct value in col.-CodePudding

| Company  | Location       |
|:--------:|:--------------:|
| Firm A   | Location 1     |
| Firm A   | Location 2     |
| Firm B   | Location 2     |
| Firm B   | Location 4     |
| Firm C   | Location 3     |
| Firm C   | Location 5     |

How can I pick Firm A & B by "looking for a company which has not facility in location 2"? Expected result: Find a company without location in "Location 2", the query gives:

|Company  |
|:--------|
|Firm C   |

CodePudding user response：

You can use an anti-join or you can use NOT EXISTS. An example of the latter is:

select *
from my_table a
where not exists (
  select 1 from my_table b 
  where b.company = a.company and b.location = 'Location 2'
)