CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12575

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

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'

Upvotes: 1

Views: 153

Answers (4)

Rupesh
Rupesh

Reputation: 342

My solution is Query 1 below.

I have also tested your query (Query 2) and tried to find out which of the two is more efficient based on 'Actual Execution Plan' and the result is as under:

Query 1: query cost (relative to batch) : 25%

select Name, COUNT(*) as cities 
from (select city from Comp where name = 'ABC') m
join (select name, city from Comp where Name <> 'ABC') c on m.city = c.city 
group by Name
having COUNT(*) = (select COUNT(*) from Comp where Name = 'ABC')

Query 2: query cost (relative to batch) : 75%

SELECT DISTINCT S.name  
FROM Comp AS S 
WHERE NOT EXISTS 
(   
    (SELECT city FROM Comp WHERE name = 'ABC') 
    EXCEPT 
    (SELECT T.city FROM Comp AS T WHERE  T.name = S.Name 
    )
)
AND S.name <> 'ABC'

NOTE: On later evaluation, I found solution by @Prdp is more efficient.

For duplicate city scenario 

select Name
from (select city from Comp where name = 'ABC') m
join (select name, city from Comp where Name <> 'ABC') c on m.city = c.city 
group by Name
having COUNT_BIG(DISTINCT c.City) = (select COUNT_BIG(DISTINCT City) from Comp where Name = 'ABC')

Upvotes: 0

Bert
Bert

Reputation: 82489

Alternatively,

declare @selectedcompany nvarchar(10) = 'abc'

select distinct name
from comp rsDistinct
where not exists
(
    select city from comp where name = @selectedcompany
    except
    select city from comp where name = rsDistinct.name
)
and name != 'ABC'

Upvotes: 1

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12575

During this time, I have written this query also :

SELECT 
  DISTINCT
  S.name  
FROM Comp AS S 
WHERE NOT EXISTS 
    (   
        (
            SELECT 
              city 
            FROM Comp 
            WHERE name = 'ABC'
        ) 
        EXCEPT 
        (   SELECT 
            T.city 
            FROM Comp AS T 
            WHERE  T.name = S.Name 
        )
    )
    AND S.name <> 'ABC'

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

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)

Upvotes: 2

Related Questions