[vox-tech] puzzling over MySQL query: joining aggregated values in a single query
Hi! I have a simple table with multiple records per id number: soil horizons from numerous pits. the top and bottom of each horizon or layer is defined in the fields 'top' and 'bottom' a quick example of a single pit's records would like like this: +-+---+--+--++ | pedon_id| hz_number | name | top | bottom | +-+---+--+--++ | SSGG-spring-05-P009 | 1 | O|0 | 3 | | SSGG-spring-05-P009 | 2 | A1 |3 | 14 | | SSGG-spring-05-P009 | 3 | A2 | 14 | 26 | | SSGG-spring-05-P009 | 4 | AB | 26 | 70 | | SSGG-spring-05-P009 | 5 | Bw1 | 70 |108 | | SSGG-spring-05-P009 | 6 | Bw2 | 108 |145 | | SSGG-spring-05-P009 | 7 | 2C | 145 |170 | | SSGG-spring-05-P009 | 8 | 3Ab | 170 |226 | | SSGG-spring-05-P009 | 9 | 3Bwb | 226 |240 | +-+---+--+--++ 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: pseudo-SQL; select table_a.* , (pedon_id, max(bottom) as depth from horizon where name REGEXP '.?C.?|R|.b.?' != 1 group by pedon_id) from table_a, horizon where i.e. is it possible to perform an aggregation in the same step as the join, or am I going to need to use some temp. tables? Any thoughts -- am i trying to do something completely ridiculous? Thanks! -- Dylan Beaudette Soils and Biogeochemistry Graduate Group University of California at Davis 530.754.7341 ___ vox-tech mailing list vox-tech@lists.lugod.org http://lists.lugod.org/mailman/listinfo/vox-tech
Re: [vox-tech] puzzling over MySQL query: joining aggregated values in a single query
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 ___ vox-tech mailing list vox-tech@lists.lugod.org http://lists.lugod.org/mailman/listinfo/vox-tech
