How to check whether ALL the rows in a table exist among a list of rows in another table?

Question

For example, I have this table that contains two specific brands - 'Ford' and 'Subaru', which just so happen to be the top earning brands for the Eastern and Western markets respectively.

To obtain this info, I make a view:

create or replace view top_brands
as 
select name from brands 
where count = (select max(count) from top_selling_east)
union 
select name from brands
where count = (select max(count) from top_selling_west)
;

I now have a table that contains the two top brands from the East and West respectively.

 name
-------
 Ford
 Subaru

Now I want to search a table that contains several dealerships, and check whether they sell BOTH Ford and Subaru ALONGSIDE any other cars, but using the views / queries from above (using 'Ford' or 'Subaru' directly would not be ideal because the year may change, and so the top selling cars may be different)

So the dealership_sells table might look like this

 dealership |       name
------------+------------------
          A | Ford
          A | Toyota
          A | Mazda
          B | Ford
          B | Subaru
          B | BMW
          C | Lexus
          C | Mercedes
          C | Aston Martin
          C | McLaren

From this table, I want to run a query that returns dealership B, as the cars they offer include ALL the data in my top_brands table + whatever else they sell.

So far I've tried these to no avail:

create or replace view top_brands_dealerships
as
select dealership from dealership_sells
where exists (select * from top_brands)
;

This returns A and B - this means it functions like the OR operator, with either Ford or Subaru alone being sufficient to return a result.

Using IN doesn't work either, behaving the same way as the above.

ANY and ALL don't work. ANY again operates the same as above, and ALL looks for dealerships that offer ONLY Ford and Subaru, rather than merely including Ford + Subaru.

Not sure how else to approach this. Maybe I've used the above incorrectly.

Answer 1

This reads like a relational division problem, where you want dealers that sell all top brands.

A typical approach uses a join to filter on the wanted brands, then having to ensure that all top brands did matach:

select d.name
from dealership_sells d
inner join top_brands b on b.name = d.name
group by d.name
having count(*) = ( select count(*) from top_brands )