Thread: Max value joining three tables with criteria

I'm wanting to bring back the max value for the month for each key that has the name KW.

here is what I have so far:

SELECT stations.name, data_definitions.name, data_values_5_2011.time, data_values_5_2011.value
FROM hist.dbo.data_definitions data_definitions, hist.dbo.data_values_5_2011 data_values_5_2011, hist.dbo.stations stations
WHERE data_definitions.osi_key = data_values_5_2011.osi_key AND data_definitions.station_id = stations.station_id AND ((data_definitions.name='KW'))
ORDER BY stations.name

thanks in advance.


here is one where I didn't join any tables and it works fine, no criteria on this one though.

SELECT data_values_min_1_2011.time, data_values_min_1_2011.osi_key, data_values_min_1_2011.value
FROM hist.dbo.data_values_min_1_2011,
(SELECT max(data_values_min_1_2011.value) as maxvalue, data_values_min_1_2011.osi_key
FROM hist.dbo.data_values_min_1_2011
GROUP BY data_values_min_1_2011.osi_key) maxresults
WHERE data_values_min_1_2011.osi_key = maxresults.osi_key
AND data_values_min_1_2011.value= maxresults.maxvalue;


this one works fine.