This is greatly simplified, but I have a database that contains the
following fields and sample data:

First,Last,Address,Gender,Age
John,Doe,123 Main,M,34
Jane,Doe,123 Main,F,32
Jacob,Doe,123 Main,M,15
Bob,Smith,234 Main,M,45
Tom,Smith,234 Main,M,43
Sara,Brown,345 Main,F,34
Nancy,Brown,345 Main,F,37
Don,Black,456 Main,M,34
Sally,Black,456 Main,F,30

I need to come up with a query that will return "married households"
using the criteria that there is a man and women listed at the same
address both over the age of 18 years old. The query needs to list
everyone at the household if the household meets the criteria (not just
the two married people)

As applied to the above data it should return these records:

John,Doe,123 Main,M,34
Jane,Doe,123 Main,F,32
Jacob,Doe,123 Main,M,15
Don,Black,456 Main,M,34
Sally,Black,456 Main,F,30

I imagine I have to use a subquery to accomplish this but I am looking
for some assistance getting a general method to work correctly. In my
actual application, there are additional fields such as City, State,
Zip and a lot of other information columns. I hope I can come up with
SQL that is flexible enough to be expanded to the full application.
Also, I didn't include a primary key in the data listed above so feel
free to include an ID field in any proposed solution. Thanks in advance.

dandiebolt (AT) gmail (DOT) com wrote:

This seems to do what you want (note I have assumed that "over the age
of 18 means 18 or over so that 18 years and 1 second is over 18):

SELECT DISTINCT p3. *
FROM people p3
JOIN people p2 USING ( Address )
JOIN people p1 ON p1.Address = p2.Address
AND p1.Gender <> p2.Gender
AND p1.Age >=18
AND p2.Age >=18

dandiebolt (AT) gmail (DOT) com wrote:


Wow, I hope that's a for-instance, and not an actual algorithm.

Sanders Kaufman wrote:

for something this simple - the OP really needs to get a good book (a good
starter is "SQL for Dummies" or go to some training... I recommend this book
not to imply or infer the intelligence of the OP, it is simply a good starting
point for a newbie...

do some searches on joins and the "where-clause".

--
Michael Austin.
Database Consultant