Re: How do I fix this query - subqueries and max fields?

Charles wrote:
> I'm querying max values of 2 date fields from 2 different tables :
> location and elev, and also a field called NOTE...and joining them
> using the PID field. The query below is working.
>
> SELECT NOTE, (SELECT MAX(A.DATE1) FROM LOCATION A WHERE A.PID =
> LOCATION.PID) AS MAXDATE1, (SELECT MAX(A.DATE2) FROM ELEVATION A
> WHERE A.PID = ELEVATION.PID) AS MAXDATE2 FROM LOCATION, ELEVATION WHERE
> LOCATION.PID = ELEVATION.PID;
>
> However, if I try to query more fields including the PID,in addition to
> what's listed above, the query does not work. Any suggestions?
>
> SELECT LOCATION.PID AS PTID, NOTE,NOTE2,NOTE3,NOTE4, (SELECT
> MAX(A.DATE1) FROM LOCATION A WHERE A.PID = LOCATION.PID) AS MAXDATE1,
> (SELECT MAX(A.DATE2) FROM ELEVATION A WHERE A.PID = ELEVATION.PID) AS
> MAXDATE2 FROM LOCATION, ELEVATION WHERE LOCATION.PID = ELEVATION.PID;
Turn this into an in-line views:

SELECT a.col1, b.col2, c.col3, c.col4
FROM (query1) A, (query2) B, sometable C WHERE ...

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)