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.

without knowing the other tables it is hard to understand your problem, your last query makes no sense, as long as there are rows you would get all rows back — nbk, Mar 11 '23 at 19:00
I think you over-complicated problem. I'm pretty sure you can get all neccesary information from `dealership_sells` table. — Maciej Los, Mar 11 '23 at 19:38
This is a faq. Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. — philipxy, Mar 11 '23 at 22:16
You want to `check whether they sell BOTH`, but at this point it's not even clear we are checking for two distinct brands. The query above uses `UNION`, not `UNION ALL`. So it might be just one. Also, no table definition for any of the tables that would tell us about `UNIQUE` and `NOT NULL` constraints, etc. These inaccuracies make the question hard to answer. The best answer depends on the exact question ... — Erwin Brandstetter, Mar 11 '23 at 22:31

Belayer · Answer 1 · 2023-03-11T19:57:12.760

You seem to have a major misunderstanding of what EXISTS actually does.

The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is “true”; if the subquery returns no rows, the result of EXISTS is “false”.

Since the view top_brands contains at least one row exists always returns True thus reducing your query to effectively `select dealership from dealership_sells'. To see the difference run:

select dealership 
  from dealership_sells
 where exists 
       (select * 
          from top_brands
         where name = 'not a top brand'
       );

You can get what you want from:

convert dealership_sells to a dealership and an array of names
convert top_brands to an array
Join the above on array #1 contains array #2. See Array Functions and Operators.

    with dealers(dealership, brands) as 
         (select dealership, array_agg(name) 
            from dealership_sells
           group by dealership
         ) --select * from dealers 
        , req_brands(brands) as 
          (select array_agg(name)
             from top_brands
          ) -- select * from req_brands 
    select dealership 
      from dealers    d
      join req_brands r
        on d.brands @> r.brands;

See Demo here

Thanks for this. Appreciate the clarification on EXISTS. Although I'm wondering if there is a simpler solution rather than resorting to array conversion? Would that be the same as using a string agg to compile the brands? — Rayyan Khan, Mar 12 '23 at 02:47

score 0 · Accepted Answer · answered Mar 11 '23 at 21:08

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 )

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

2 Answers2