'Select' max from 2 tables and join

From: Charles <charles.amith_at_gmail.com>
Date: 12 Oct 2005 16:29:21 -0700
Message-ID: <1129159761.468446.299210@g44g2000cwa.googlegroups.com>

I have 2 tables: LOCATION and ELEVATION

In location, I would like to find the record with the max value for field: DATE1

In elevation, I would like to find the record with the max value for field: DATE2

I would like to join the 2 tables using the common field: PID , and also query a field called NOTE from the "LOCATION" table.

I've tried:

"SELECT NOTE, (SELECT MAX(DATE1) FROM LOCATION) AS MAXDATE1, (SELECT MAX(DATE2) FROM ELEVATION) AS MAXDATE2 FROM LOCATION,ELEVATION WHERE LOCATION.PID=ELEVATION.PID" So far, this doesn't work. I've tried variations, that have a result, but the note is not in synch with the date values. Any suggestions? Received on Wed Oct 12 2005 - 18:29:21 CDT