List:General Discussion« Previous MessageNext Message »
From:Peter Normann Date:June 16 2002 6:00pm
Subject:RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
View as plain text  
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:savage@stripped] 
Sent: 16. juni 2002 19:38
To: mysql@stripped
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" <peter@stripped>
To: "'Chris Knipe'" <savage@stripped>; <mysql@stripped>
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:savage@stripped]
> Sent: 16. juni 2002 19:00
> To: mysql@stripped
> 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" <peter@stripped>
> To: "'Chris Knipe'" <savage@stripped>; <mysql@stripped>
> 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:savage@stripped]
> > Sent: 16. juni 2002 15:43
> > To: mysql@stripped
> > 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 <mysql-thread112107@stripped>
> > To unsubscribe, e-mail
> > <mysql-unsubscribe-peter=normann.com@stripped>
> > 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 <mysql-thread112128@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-peter=normann.com@stripped>
> 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 <mysql-thread112132@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-peter=normann.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


Thread
LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....Chris Knipe16 Jun
  • RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....Peter Normann16 Jun
  • RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....Peter Normann16 Jun
    • Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....Chris Knipe16 Jun
      • RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....Peter Normann16 Jun
        • Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....Chris Knipe16 Jun
          • RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....Peter Normann16 Jun