[thelist] SQL - Return tuple only if ALL attributes same thing
Matt Warden
mwarden at gmail.com
Wed May 18 12:22:17 CDT 2005
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.
More information about the thelist
mailing list