Search code examples
sqlpostgresqlrelational-division

Finding elements that appear in "every" category in a column

We are given this schema for a table called "Orders":

CREATE TABLE country(
    orderID VARCHAR,
    customerID INTEGER,
    shipperID VARCHAR
)

Here is a visualization of this table, taken from w3school's SQL tutorial:

enter image description here

I want to get the customerIDs which have ordered at least once from every shipperID.

One way to do it would be recognize there are only three unique shipperIDs (1, 2 and 3) so we could perform three cartesian products and identify the customerIDs that way. However, I want to write the query in such a way that it wouldn't matter how unique many shipperIDs there are.

With relational algebra, this can be done fairly simply with division. Is there an easy way do do query like this in SQL?

Solution

  • Could be you can use a group by .. having and a subselect 

    select CustomerID 
from Orders 
group by CustomerID
having count(distinct ShipperID) = ( select count(distinc ShipperID) from Orders)

    The group by give the number of shipperID for each CustomerID .. the having check if this is equal to the total of the ShipperID