SELECT tlb1.DepartureLocation AS Departure, tbl1.DepartureLocation AS Destination LEFT JOIN DepartureLocation ON tlb2.DepartureID=tlb1.LocationID LEFT JOIN DepartureLocation ON tbl2.ArrivalID=tbl1.LocationID ORDER BY tbl2.Time etc etc etc
I get
ERROR 1066 (42000): Not unique table/alias: 'tbl1'
The statement above looks a bit strange, there is no FROM clause, and there seems to be 4 tables involved, named tbl1, tlb1, tbl2 and tlb2? According to your table description above, there is no column named "DepartureLocation"? According to the query, it seems like a table has that name?
Sorry, was trying to keep things simple and made a couple of typos (yes, silly of me, I know).
I'll pretend you have two tables named tbl1 and tbl2, containing the columns you described above:
tbl1: LocationID, Location tbl2: DepartureID, ArrivalID, Time
Then try something like this:
SELECT Departure.Location, Arrival.Location AS Destination, Time FROM tbl2 LEFT JOIN tbl1 AS Departure ON Departure.LocationID = DepartureID LEFT JOIN tbl1 AS Arrival ON Arrival.LocationID = ArrivalID ORDER BY Time
Note that there are two different uses of alias in this statementtable alias and column alias: the table "tbl1" is aliased twice, to "Departure" and "Arrival". Aliasing a table is necessary to be able to join the same table multiple times. The "Arrival.Location" column is aliased to "Destination". This is necessary to avoid two columns from having the same name, in this case both columns would have been named "Location".
YAY :) Ok, that does explain allot, and it does work yes. Thank you VERY much.
mysql> SELECT FlightData.PilotCallSign AS FLNum,
-> FlightLogs.Tracker AS Tracker,
-> FlightLogs.TimeStart AS TimeDep,
-> Departure.IATA AS AirpDep,
-> Arrival.IATA AS AirpDes,
-> FlightLogs.Plane AS Plane
-> FROM FlightLogs
-> LEFT JOIN Airports AS Departure ON FlightLogs.AirpDep=Departure.ICAO
-> LEFT JOIN Airports AS Arrival ON FlightLogs.AirpDes=Arrival.ICAO
-> LEFT JOIN FlightData ON FlightLogs.Tracker=FlightData.Tracker
-> GROUP BY FlightData.Tracker
-> ORDER BY FlightLogs.TimeStart
-> LIMIT 30;
+---------+--------------------------------------+---------------------+---------+---------+----------+
| FLNum | Tracker | TimeDep | AirpDep | AirpDes | Plane |
+---------+--------------------------------------+---------------------+---------+---------+----------+
<SNIP>
+---------+--------------------------------------+---------------------+---------+---------+----------+
30 rows in set (15.52 sec)
My next question now, would be where to INDEX. Size is a MAJOR issue here. Two days worth of data, and I am already sitting with a DB over 60MB. The above query is simply for debug / testing purposes. There will be a WHERE clause, i.e. WHERE FlightLogs.CCID='blah' I will more than likely also only query BETWEEN certain dates, based on the FlightLogs.StartTime - thus, also no LIMIT.
Airports have INDEX on Name, CountryID, ICAO as well as IATA.
FlightData have INDEX on PilotCID, PillotCallSign, and Tracker.
FlightLogs have INDEX on Tracker(Unique), CCID, AirpDes, AirpDep, and TimeStart
Thus, all columns used for the JOINs, have indexes already? All GROUP BYs have indexes already? Why is this taking so long????? FlightData has just over 130.5K records, Airports just over 23K records. Airports won't grow much anymore, FlightData will grow to a ENOURMOUSE size, FlightLogs will be exponentially smaller than FlightData - but also quite big over time...
Hope you can be of assistance again :)
-- Chris
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
