Search code examples
sqlpostgresqlselectrelational-division

PostgreSQL: select all types that have an entry corresponding to all entries in another table


I have two tables, producers and skis. Producers have their ID, and each model of skis knows the ID of its producer, each model also has a type, which is not unique among skis. I need to select all types that are produced by all of the producers (regardless of models). I wrote a query:

select type 
from skis s
where not exists (
    select name from producers p
    except
    (
        select name 
        from producers p
        where (p.name=s.producer)
    )
);

It only works when I have 1 ski and one producer. What is a good way to do that?

EDIT for clarification: in the producer table, the column 'name' is their ID, and in the ski table the producers ID column is 'producer'.


Solution

  • Count the number of producers per type and compare with the total number of producers:

    select type
    from skis
    group by type
    having count(distinct producer) = (select count(*) from producers);