How can I find all companies that have branch in all cities of another company

Question

I use SQL server 2014. I have a table like this:

CREATE Table Comp
(
  ID int identity(1,1),
  City varchar(50),
  Name varchar(50)
)

I have these record in my table

INSERT INTO Comp
values 
  ('Montreal','ABC'),
  ('QuebecCity','ABC'),
  ('Mont-tremblant','ABC'),
  ('Saint-donant','ABC'),
  ('Saint-savaure','ABC'),

  ('Montreal','QQQ'),
  ('QuebecCity','QQQ'),
  ('Mont-tremblant','QQQ'),
  ('Saint-donant','QQQ'),
  ('Saint-savaure','QQQ'),

  ('Montreal','www'),
  ('QuebecCity','www'),
  ('Mont-tremblant','www'),

  ('Montreal','dd'),
  ('QuebecCity','dd'),
  ('Mont-tremblant','dd'),
  ('Saint-donant','dd'),
  ('Saint-savaure','dd'),  
  ('trois rivieres','dd'),
  ('perce','dd'),

  ('City1','SSS'),
  ('City2','SSS'),
  ('City3','SSS'),
  ('City4','SSS'),
  ('Saint-savaure','SSS'),  
  ('City6','SSS'),
  ('City7','dd')

How can I query just the company's name which has the cites in all of the cites in other Company

For example if my master Company is 'ABC', It should query 'QQQ' and 'dd'

Answer 1

Here is one way

SELECT NAME
FROM   comp c1
WHERE  City IN (SELECT city
                FROM   comp
                WHERE  NAME = 'ABC')
       AND NAME <> 'ABC'
GROUP  BY NAME
HAVING Count(DISTINCT City) = (SELECT Count(DISTINCT city)
                               FROM   comp
                               WHERE  NAME = 'ABC') 

If you don't have duplicate city for each name then a better approach

SELECT c1.NAME
FROM   comp c1
       JOIN (SELECT city,
                    Count(1)OVER() AS cnt
             FROM   comp
             WHERE  NAME = 'ABC') c2
         ON c1.City = c2.City
WHERE  c1.NAME <> 'ABC'
GROUP  BY c1.NAME
HAVING Count(c1.city) = Max(cnt)