[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