What will be the SQL-Query for the following?

Question

Tables :
employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)

Query : Assume that the companies may be located in several cities. Find all companies located in every city in which 'Small Bank Corporation' is located.

I tried :
select cname from company c where (select city from company c2 where c2.cname='Small Bank Corporation' MINUS select city from company where cname=c.cname) is null;

and several variations like these but it does not work at all. My idea is to generate a set A and Set B. Set B is all cities where company Small bank is located. Say Set B = {Mumbai,Pune} . Now I was trying to generate set A for each company in company table. Say for the first loop, cname = 'FBC' then Set A will contain all cities where FBC is located in.
Now, Set B - Set A if this part becomes null, that means A is atleast located in all cities as SBC and I was trying to print that set A's Company name. But for some reason, this is not working and I'm not able to figure it out.

Oracle error : single-row subquery returns more than one row.

Any thoughts on this? Thankyou

Answer 1

First of all: The data model is a tad inappropriate, because the table company does not contain one row per company. A better name would be company_branch_office for instance. We should keep this in mind when writing the query, because the query may read different from what it actually does. (If you can, change the table name.)

Anyway, getting the companies that have a branch office in each and every city where the Small Bank Corporation is located, too, is not an easy task. The simplest way I can think of is this:

select company_name
from company
where city in
(
  select city
  from company
  where company_name = 'Small Bank Corporation'
)
group by company_name
order by count(distinct city) desc
fetch first row with ties;

How does this work?

1. I fetch all the branch offices located in the Small Bank Corporation cities. Thus I get the Small Bank Corporation branch offices themselves plus all other companies' branch offices in the same cities.
2. I then aggregate by company to get the count of the companies' branch offices in these cities. I need COUNT(DISTINCT city) here, because a company may have two or more offices in one city.
3. I order the companies by their number of matching cities and only keep those companies that have the maximum count (i.e. all cities). This applies to Small Bank Corporation of course and maybe other companies - the companies that we are looking for.

Answer 2

You can achieve this by using EXISTS:

SELECT * FROM
COMPANY
WHERE 
name != 'Small Bank Corporation'
AND EXISTS
(SELECT 1 FROM 
COMPANY as SBC WHERE 
SBC.name= 'Small Bank Corporation'
AND SBC.city = COMPANY.city)

Answer 3

Thorsten's answer is fine for the question you stated, but I think the query is brittle. If you have a filter in the outer query and "Small Bank Corporation" is not included, then the query will return the companies with the most overlapping cities -- even though there might be none that overlap all.

I would do an explicit check using a HAVING clause:

select c.company_name
from company c
where exists (select 1
              from company c2
              where c2.city = c.city and
                    c2.name = 'Small Bank Corporation'
             )
group by c.company_name
having count(*) = (select count(*)
                   from company c2
                   where c2.name = 'Small Bank Corporation'
                  );

Note that this uses COUNT(*), under the assumption that companies do not have multiple rows in a city. If that is possible, then use count(distinct city) in both places.