Sakeeb Hossain Sakeeb Hossain - 1 year ago 54
SQL Question

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

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

customerID INTEGER,

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?

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download