I think you need to use the following query:
mysql> SELECT weather_data.Temp,
-> weather_locations.Name
-> FROM weather_data
-> LEFT JOIN weather_locations ON
-> weather_locations.MetarCode = weather_data.Site
-> WHERE weather_locations.MetarCode IS NULL;
Note the LEFT JOIN weather_locations in stead of weather_data
Peter Normann
-----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
