Question 89427 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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:

    1. convert dealership_sells to a dealership and an array of names
    2. convert top_brands to an array
    3. 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

    Login or Signup to reply.
  2. 0

    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 )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search