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
