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

Paul Cowan evolt at funkwit.com
Thu May 19 06:27:23 CDT 2005


On 18/05/2005 11:53 AM -0500 Jacques Capesius wrote:
> 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.

Or, to use an alternative approach, use GROUP BY to filter the results of 
the aggregate function:

SELECT
	Customers.Name
	COUNT(DISTINCT #Trucks.Model) AS DifferentTrucks
FROM
	Trucks
INNER JOIN Deliveries on
	Deliveries.RegNumber = Trucks.RegNumber
INNER JOIN Customer on
	Customer.CustomerID = Deliveries.CustomerID
GROUP BY
	Customer.Name
HAVING
	COUNT(DISTINCT #Trucks.Model) = 1

Cheers,

Paul


More information about the thelist mailing list