I have one table(Flights) which holds a FlightID, DepartFrom, and ArriveAt fields, there all ints. The fields DepartFrom and ArriveAt contain AirportID's.
Another table holds the airports. This table has an AirportID and and Airport field, the
the AirportID is an int and the Airport holds the name of the Airport.
I want to select the Aiports which are referred to by the DepartFrom and ArriveAt Fields in one query. Here is my bad attempt at doing it in one query.
SELECT Airport, Airport
FROM Flights, Airports
Where DepartFrom=AirportID and
ArriveAt=Airport.ID;
How do I pick up the other airport in the same query, since they both have the field name SQL gives an error to my query.
At the moment I'm using 2 query's and running them through a while loop together to output to my page. There has got to be an easier way of doing this.
you need to relate the tables together and then use JOINS in your SQL statement to do what you want if I'm reading the question right. How to do this would be found in any SQL book, probably even an online tutorial. The ability to do this is what makes relational databases relational.
The trick here is that you need to join with your Airports table twice -- once for relating to the DepartFrom ID and once for relating to the ArriveAt ID. To do this, you must list the same table twice in the FROM clause of your SELECT statement, and assign the table two different "aliases" using the SQL keyword "AS"; one for each use of the table. You also assign aliases for the two Airport columns in the result set so you can tell the Origin apart from the Destination.
The exact query is as follows:
SELECT A1.Airport AS Origin, A2.Airport AS Destination
FROM Flights, Airports AS A1, Airports AS A2
WHERE DepartFrom=A1.ID and ArriveAt=A2.ID;
Advanced SQL tricks like this will be covered in Part 9 of my article series on SitePoint.com.
Give us a yell if this doesn't do what you want it to!
Bookmarks