sql - Retrieving Rain Current Rate from 2 MySQL tables -

i have 2 sql tables:

• pluviometers (id, lat, lng, desc)
• rains (id, pluviometer_id, date, value)

table fields , structure self-explanatory.

the remarkable point 'rains' cumulative table, i.e., value field cumulative amount of rain, , respective dates moments when value increases.

with of in mind, want execute sql query obtains list of pluviometers , attributes plus 1 field, rain amount from given date query execution time.

my best try, far:

    select      pluviometers.*,      lastdate,      lastvalue,     firstdate,     firstvalue,     rain =      case firstdate         when null 0         else (lastvalue - firstvalue) / (lastdate - firstdate)     end  pluviometers  left join (      select          h.pluviometer_id pid,         max(h.date) lastdate,          h.value lastvalue      rains h      left join (          select              h2.pluviometer_id pid2,             min(h2.date) firstdate,              h2.value firstvalue               rains h2             h2.date > ###### given date ######              group pid2             order pid2 asc          ) p2 on pid2 = h.pluviometer_id      group pid     order pid asc  ) p on pid = pluviometers.id  group pluviometers.id order pluviometers.id asc 

so far, #1054 - unknown column 'firstdate' in 'field list' error server.

any appreciated. in advance.

firstdate , firstvalue selected h2 create result set p2, don't select p2.firstdate or p2.firstvalue combined result set rains h left join p2.

so add them first sub-select clause here:

... select      h.pluviometer_id pid,     max(h.date) lastdate,      h.value lastvalue,     p2.firstdate,     p2.firstvalue  rains h ... 

this make them visible outer select clause @ top.

also, change syntax selecting rain field:

not

rain =      case firstdate         when null 0         else (lastvalue - firstvalue) / (lastdate - firstdate) 

but instead

case firstdate     when null 0     else (lastvalue - firstvalue) / (lastdate - firstdate) end rain