[thelist] SQL - Return tuple only if ALL attributes same thing

[Matt Warden]

Tim,

On 5/18/05, Tim Burgan <email at timburgan.com> wrote:
> Hello,
> 
> In my DB I have 2 tables, 'Deliveries' and 'Trucks'.
> Deliveries contains a foreign key RegNumber for a truck that makes a
> Delivery.
> Trucks has the RegNumber as primary key, and another column called Model.
> 
> In the select statement, how do I return results where every single
> delivery to a certain customer (customerID in Deliveries table) has been
> made by the same model truck?
> 
> I thought the operator 'ALL' may work, but didn't.
> Does anyone have any suggestions?

Something like:

SELECT *
FROM Deliveries a
WHERE customerID = X
     AND NOT EXISTS (
          SELECT *
          FROM Deliveries b
          WHERE b.customerID = X
               AND b.RegNumber != a.RegNumber
)

In other words, select all tuples for this customer where there exists
no delivery to this customer with a different regnumber.


-- 
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.