List:General Discussion« Previous MessageNext Message »
From:Peter Normann Date:June 16 2002 5:34pm
Subject:RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
View as plain text  
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


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