I've read almost all of the messages relating to this but I'm stuck .. any
advice gratefully received ! Thanks ...
Some people say translate INNER JOINS from Access to WHERE clauses, some say
to remove the nesting (delete brackets/parens) and re-write the INNER JOIN
as an INNER JOIN in mySQL inside-out .. I am wondering if there is a
scientific method of translating MS Access INNER JOINS to mySQL ?
The examples in the threads are (unfortunately) too simple for my case .. I
have difficulty distilling a scientific method from them .. I have 6 tables,
three of them duplicated via aliases (as they are for lookups) and my nested
INNER JOIN from Access is 6 levels deep .. :-(
What to do .. ? Any ideas ?
The Access SQL is first (which returns, as expected in Access in my case, 1
row as a result) followed by my version (which returns no data ..
incorrect). I know this is complex but normalisation increased my table
count but removed data duplication .. as all good SQL'ers should do !
Tables are:
=========
Routes (routes between two airport IDs)
Flights (flight numbers for airlines and aircraft ID)
Aircraft (aircraft details)
Airports (airport name and ICAO/IATA codes)
AirportLink (lookup table between airport ID and city ID)
Cities (city name and details)
( NOTE in Access code : Last three are "duplicated" in the query as
"tablename_1" )
( NOTE in mySQL code : Last three are renamed via aliases .. e.g.
DEPARTAIRPORT and ARRIVALAIRPORT)
Access : (no "custom" aliases)
=======================
SELECT
Routes.RouteID, Flights.FlightNo, Aircraft.AircraftType, Cities.CityName,
Airports.AirportName, Cities_1.CityName, Airports_1.AirportName
FROM
Aircraft INNER JOIN (((((Routes INNER JOIN Flights ON Routes.FlightNumber =
Flights.FlightID) INNER JOIN (Cities INNER JOIN (Airports INNER JOIN
AirportLinks ON Airports.AirportID = AirportLinks.AiportID) ON Cities.CityID
= AirportLinks.CityID) ON Routes.Origin = Airports.AirportID) INNER JOIN
Airports AS Airports_1 ON Routes.Destination = Airports_1.AirportID) INNER
JOIN AirportLinks AS AirportLinks_1 ON Airports_1.AirportID =
AirportLinks_1.AiportID) INNER JOIN Cities AS Cities_1 ON
AirportLinks_1.CityID = Cities_1.CityID) ON Aircraft.AircraftID =
Flights.AircraftType
GROUP BY
Routes.RouteID, Flights.FlightNo, Aircraft.AircraftType,
Airports_1.AirportName, Cities_1.CityName, Airports.AirportName,
Cities.CityName;
mySQL version (with my "custom" aliases to help clarify relationships)
====================================================
SELECT
Routes.RouteID, Flights.FlightNo, Aircraft.AircraftType, DEPARTCITY.CityName
AS 'Departure city', DEPARTAIRPORT.AirportName AS 'Departure airport',
ARRIVALCITY.CityName AS 'Arrival city', ARRIVALAIRPORT.AirportName AS
'Arrival airport'
FROM
Aircraft, Routes, Flights, Cities AS DEPARTCITY, Cities AS ARRIVALCITY,
Airports AS DEPARTAIRPORT, Airports AS ARRIVALAIRPORT, AirportLinks AS
LINK1, AirportLinks AS LINK2
WHERE
Routes.FlightNumber = Flights.FlightID AND
DEPARTAIRPORT.AirportID = LINK1.AirportID AND
DEPARTCITY.CityID = LINK1.CityID AND
Routes.Origin = DEPARTAIRPORT.AirportID AND
Routes.Destination = ARRIVALAIRPORT.AirportID AND
ARRIVALAIRPORT.AirportID = LINK2.AirportID AND
LINK2.CityID = ARRIVALCITY.CityID AND
Aircraft.AircraftID = Flights.AircraftType;
GROUP BY
Routes.RouteID, Flights.FlightNo, Aircraft.AircraftType,
ARRIVALAIRPORT.AirportName, ARRIVALCITY.CityName, DEPARTAIRPORT.AirportName,
DEPARTCITY.CityName;
End
Kemal Danisman
Amatuer mySQL/PHP developer
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
