[vox-tech] puzzling over MySQL query: joining aggregated values in a single query

[Chris Jenks]

On Tue, 11 Apr 2006, Dylan Beaudette wrote:

>[...]
> I am able to select out the soil depth, excluding certain horizons with the
> following query:
>
> select pedon_id, max(bottom) as depth from horizon where name REGEXP '.?C.?|
> R|.b.?' != 1 group by pedon_id ;
>
> a simple result for the above example would be:
> +---------------------+-------+
> | pedon_id            | depth |
> +---------------------+-------+
> | SSGG-spring-05-P009 |   145 |
> +---------------------+-------+
>
> This works well, except for when I would like to join this aggregated
> information to a new table in a single query. Is it possible to do something
> like this:

   What query did you try, and what error did you get? I assume you would 
join the tables, and then distinguish ambiguous column names by leading 
tham with the corresponding table names in the query. I've used MSSQL more 
than MySQL, but this seems like it should not be a problem.

   Yours,

     Chris