[thelist] SQL - Return tuple only if ALL attributes same thing
Jacques Capesius
Jacques.Capesius at compellent.com
Wed May 18 11:53:19 CDT 2005
So, your table structure looks like this, correct?
TABLE: Customer
---------------------------------------------------
CustomerID (PK) | Name
---------------------------------------------------
1 | John Doe
2 | Bob Smith
3 | Mr. T
TABLE: Trucks
---------------------------
RegNumber (PK) | Model |
---------------------------
1 | Volvo 1246
2 | Mack 4543
3 | Trogdor 9000
4 | Volvo 1246
TABLE: Deliveries
------------------------------------------------------
DeliveryNumber (PK) | RegNumber(FK) | customerID (id)
------------------------------------------------------
1 | 1 | 3
2 | 1 | 2
3 | 2 | 1
4 | 2 | 4
5 | 3 | 1
6 | 4 | 3
And you wish to return Mr. T, cuz all his deliveries were done with
Volvo 1246 trucks?
I would start by trying to figure out how to get the number of different
model types for a particular customer.
SELECT count(distinct model)
FROM Trucks
INNER JOIN Deliveries on Deliveries.RegNumber = Trucks.RegNumber
INNER JOIN Customers on Customers.CustomerID = Deliveries.CustomerID
WHERE Customers.CustomerID = 3;
This should return 1, cuz Mr. T has two deliveries, but they were both
made with a Volvo 1246.
We want to only return customers who get a value of 1 when this query is
run, so I took this query and nested it within the filter criteria of a
more general query.
I got this:
SELECT DISTINCT cust.Name, Trucks.Model
FROM Trucks
INNER JOIN Deliveries on Deliveries.RegNumber = Trucks.RegNumber
INNER JOIN Customers cust on cust.CustomerID = Deliveries.CustomerID
WHERE
(
SELECT count(distinct model)
FROM Trucks
INNER JOIN Deliveries on Deliveries.RegNumber = Trucks.RegNumber
INNER JOIN Customers on Customers.CustomerID =
Deliveries.CustomerID
WHERE Customers.CustomerID = cust.customerid
) = 1;
Which seems to be working.
HTH!
More information about the thelist
mailing list