Re: SQL Joins question

On 4 Apr, 22:26, jeff.lawre..._at_gmail.com wrote:
> 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!

This is pure guesswork but you could try something like:

SELECT A.House, B.Room, MAX(D.Age) AS MaxAge FROM House AS A
INNER JOIN Room AS B
 ON A.House = B.House
INNER JOIN Occupants AS C
 ON B.Room = C.Room
INNER JOIN People AS D
 ON C.Person = D.Person
GROUP BY A.House, B.Room ;

--
David Portas