Does SELECT weather_data.Site, weather_data.Temp, MAX(weather_data.Date) AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS';
Work? You can add LIMIT 1 to the query... Peter Normann -----Original Message----- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 19:00 To: [EMAIL PROTECTED] Subject: Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one.... Ok, It seems I spoke to soon here... SELECT weather_data.Temp AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; That, does what I want... However, there's a catch now, that I became aware of after the duplicated data started filling the weather_data table. mysql> SELECT * FROM weather_data WHERE Site='FACT'; +---------+----------------------------------------------------+-------- ---- ---------+------+-----------+---------------+------+----------+ | EntryID | RawData | Date | Site | WindSpeed | WindDirection | Temp | DewPoint | +---------+----------------------------------------------------+-------- ---- ---------+------+-----------+---------------+------+----------+ | 5 | FACT 161200Z 31008KT 9999 FEW030 17/11 Q1021 NOSIG | 2002-06-16 12:00:00 | FACT | 19 | Northwest | 17 | 11 | | 36 | FACT 161300Z 31009KT 9999 FEW030 16/10 Q1021 NOSIG | 2002-06-16 13:00:00 | FACT | 21 | Northwest | 16 | 10 | | 64 | FACT 161400Z 30007KT 9999 FEW030 16/11 Q1020 NOSIG | 2002-06-16 14:00:00 | FACT | 16 | Northwest | 16 | 11 | | 73 | FACT 161500Z 30007KT 9999 FEW030 15/11 Q1020 NOSIG | 2002-06-16 15:00:00 | FACT | 16 | Northwest | 15 | 11 | +---------+----------------------------------------------------+-------- ---- ---------+------+-----------+---------------+------+----------+ 4 rows in set (0.13 sec) The problem now, is that the above query will return all four records for the FACT site as specified in my WHERE clause. Is there a way that I can add to that query, the funcionailty to: -- Order by reverse Date, and show only 1 record for every site specified by the WHERE Clause (i.e., only get the newest data for each site I specified). OR -- Use a SELECT DISTINCT so that I can have distinct SITE values in the JOIN query... Thanks... ----- Original Message ----- From: "Peter Normann" <[EMAIL PROTECTED]> To: "'Chris Knipe'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, June 16, 2002 5:44 PM Subject: RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one.... > Sorry, I didn't read the mail through before replying... :-/ > > -----Original Message----- > From: Chris Knipe [mailto:[EMAIL PROTECTED]] > Sent: 16. juni 2002 15:43 > To: [EMAIL PROTECTED] > Subject: LEFT JOIN again... Although, this time I think it's a design > flaw rather than a query one.... > > > Hiya again, > > Ok, based on all the info, feedback and help I got the last time, I was > able to construct the following query: > > mysql> SELECT weather_data.Temp, > -> weather_locations.Name > -> FROM weather_data > -> LEFT JOIN weather_data ON > -> weather_locations.MetarCode = weather_data.Site > -> WHERE weather_locations.MetarCode IS NULL; > ERROR 1066: Not unique table/alias: 'weather_data' > > The tables look as follows: > mysql> DESCRIBE weather_data; > +---------------+--------------+------+-----+---------------------+----- > +---------------+--------------+------+-----+---------------------+---- > -------+ > | Field | Type | Null | Key | Default | > Extra > | > +---------------+--------------+------+-----+---------------------+----- > +---------------+--------------+------+-----+---------------------+---- > -------+ > | EntryID | bigint(20) | | PRI | NULL | > auto_increment | > | RawData | varchar(250) | | MUL | | | > | Date | datetime | | MUL | 0000-00-00 00:00:00 | | > | Site | varchar(4) | | MUL | | | > | WindSpeed | tinyint(4) | YES | MUL | NULL | | > | WindDirection | varchar(250) | YES | MUL | NULL | | > | Temp | tinyint(4) | YES | MUL | NULL | | > | DewPoint | tinyint(4) | YES | MUL | NULL | | > +---------------+--------------+------+-----+---------------------+----- > +---------------+--------------+------+-----+---------------------+---- > -------+ > 8 rows in set (0.29 sec) > > mysql> DESCRIBE weather_locations; > +------------+--------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +------------+--------------+------+-----+---------+----------------+ > | LocationID | tinyint(4) | | PRI | NULL | auto_increment | > | MetarCode | varchar(4) | | UNI | | | > | Name | varchar(250) | | UNI | | | > | Province | varchar(250) | YES | MUL | NULL | | > +------------+--------------+------+-----+---------+----------------+ > 4 rows in set (0.03 sec) > > The basic background.... It's used to compile a history of weather > information on approximately 75 locations in my country (ala .za). The > weather_locations table, holds unique rows describing the various > locations of the weather stations. In this table, MetarCode is a 4 > character key identifing the weather station, while Name represents the > physical location (such as the Town or airport). > > In my weather_data table, I save the actual weather data. I cannot use > UNIQUE keys in this table, because I need to build up a history of the > weather changes. In this table, Site represents the same weather > station identifier as MetarCode in the weather_locations table. So, I'm > trying to accomplish my JOIN on weather_locations.MetarCode and > weather_data.Site. > > Basically, the data that I want to have returned: > SELECT weather_locations.Name, weather_data.Temp FROM weather_data WHERE > weather_data.Site='FACT'; > > Yes, this is a broken query, but I think it would give a good idea of > what I want.... I want the weather site's name from the > weather_locations table, plus whatever column I want from the > weather_data table (such as temprature, WindSpeeds, WindDirection, etc). > > > Bah... > > While typing this and playing arround some more, I got it working.... > > SELECT weather_data.Temp AS Temp, > weather_locations.Name AS Name > FROM weather_locations > LEFT JOIN weather_data ON > weather_locations.MetarCode = weather_data.Site > WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR > weather_data.Site='FAJS'; > > Thanks anyways :-) > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
