List:General Discussion« Previous MessageNext Message »
From:Peter Normann Date:June 16 2002 3:44pm
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 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


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