Subquery error in MySQL with max()

Question

I'm trying a subquery in MySQL using max(), and I keep running into an error. The gist of the query is below (though I've changed the field names).

select table1.field1, table1.field2, table2.field3, table2.field4, table3.field5, 
       (select max(age) 
          from age_table 
         where age_table.person = table2.person) 
  from table1 
inner join table2 on table2.person = table1.person 
inner join table3 on table3.person = table1.person 
inner join age_table on age_table.person = table1.person

When I try this, I get a syntax error that points to

'from age_table where age_table.person=table2.person'

...but I can't figure out what the problem is.

Answer 1

You need to create an alias for your subquery eg:

(select max(age) from age_table where age_table.person = table2.person) temp

and leave rest of the things as they are.

Answer 2

Use table aliases to differentiate between tables, without having to use the full table name:

SELECT t1.field1, t1.field2, t2.field3, t2.field4, t3.field5, 
       (SELECT MAX(at.age) 
          FROM AGE_TABLE at
         WHERE at.person = t2.person) AS max_age
  FROM TABLE1 t1
  JOIN TABLE2 t2 ON t2.person = t1.person 
  JOIN TABLE3 t3 ON t3.person = t1.person 

I removed what appeared to be a redundant JOIN to the AGE_TABLE, seeing as it wasn't used in the SELECT clause.

It's also good habit to define a column alias for derived column values - makes them easier to reference. See "max_age" for an example.