SQL Joins question

From: <jeff.lawrence_at_gmail.com>
Date: 4 Apr 2007 14:26:15 -0700
Message-ID: <1175721975.807934.170920@q75g2000hsh.googlegroups.com>

I'm attempting to perform a SQL query that joins two tables based on the parent's parent but my SQL isn't that good and am looking for assistance. Here's essentially how the data structure is setup:

A house (Table A) can have up to two rooms (Table B) and each room can have one or more occupants of various ages (Table C and Table D for this example with 2 occupants (records) in each table)

What I would like to do is determine the maximum age of occupants in each room in each house.

So if I have
House A
---Room1

     ----Sally - Age 5
     ----Jimmy - Age 10

---Room2

-----Billy - Age 8
-----Betty - Age 7

my results would be:
House Room1MaxAge Room2MaxAge

A           10                        8

Determining the max age per room isn't my difficulty, it's connecting the occupants to house A.

Help! Received on Wed Apr 04 2007 - 16:26:15 CDT