SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2000
    Location
    Cork, Ireland
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    By the way I'm using PHP4 and MySQL.

    Help me please......

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Chris


  3. #3
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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!


    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2000
    Location
    Cork, Ireland
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Kevin,

    Works Perfect!!!!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •