Sorry, I forgot: 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' GROUP BY weather_data.Site;
-----Original Message----- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 19:38 To: [EMAIL PROTECTED] Subject: Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one.... mysql> 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'; ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP colum ns is illegal if there is no GROUP BY clause mysql> Hmmm, I wonder what the manual can tell me about group by... I'll have a look and see what I can come up with. If you know what's missing, I wont mind finding out :-) Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 ----- Original Message ----- From: "Peter Normann" <[EMAIL PROTECTED]> To: "'Chris Knipe'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, June 16, 2002 7:34 PM Subject: RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one.... > 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 --------------------------------------------------------------------- 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
