Robert,

As this thread shows there are often several valid approaches to generating a desired result set. In one of my applications, users often wish to include/exclude all members of a household when one or more member qualifies based on selected criteria. Below is a simple example that uses derived tables to find members of a household with an opinion ='No' where at least one other household member has an opinion of 'Yes':

-- paste into QA

CREATE TABLE test

(ID bigint identity primary key, FirstName varchar(15), LastName varchar(20), Household char(1), Opinion varchar(10))

INSERT INTO test (FirstName, LastName, Household, Opinion)

VALUES ('Joe', 'Jones', 'A','Yes')

INSERT INTO test (FirstName, LastName, Household, Opinion)

VALUES ('Sam', 'Jones', 'A','No')

INSERT INTO test (FirstName, LastName, Household, Opinion)

VALUES ('Mary', 'Jones', 'A','Yes')

INSERT INTO test (FirstName, LastName, Household, Opinion)

VALUES ('Joe', 'Smith', 'B','Yes')

INSERT INTO test (FirstName, LastName, Household, Opinion)

VALUES ('Sam', 'Smith', 'B','No')

INSERT INTO test (FirstName, LastName, Household, Opinion)

VALUES ('Mary', 'Smith', 'B','No')

/* statement shows fields from both 'test' and derived table

'hh' to illustrate the technique. Note that the DISTINCT keyword is used because each member of a household in 'hh' with an option of 'Yes' returns all records from 'test' where Opinion='No', resulting in duplicates.

*/

SELECT DISTINCT * FROM test

INNER JOIN test as hh ON test.Household = hh.Household

WHERE hh.Opinion='Yes' and test.Opinion='No'

-- David Terrie