I am focusing on a homework project and I am supposed to carry out a database query which finds plane tickets either through the city title or even the airport terminal code, however the plane tickets table only consists of the airport terminal codes therefore if I wish to search by city I must join around the international airports table.

The international airports table has got the following posts: code, city
The plane tickets table has got the following posts: air travel, flt_no, fairport, tairport, depart, arrive, fare
The posts fairport and tairport would be the from and to airport terminal codes.
The posts depart and arrive are dates of departure and arrival.

I emerged having a query which first joins the plane tickets around the fairport column and also the international airports.code column. To ensure that me to complement the tairport I must perform another join around the previous matches in the first join.

Choose air travel, flt_no, fairport, tairport, depart, arrive, fare

    FROM (Choose * FROM plane tickets

        INNER JOIN international airports

        ON plane tickets.fairport = international airports.code

        WHERE (international airports.code = '?' OR international airports.city='?')) AS matches

    INNER JOIN international airports

    ON matches.tairport = international airports.code

    WHERE (international airports.code = '?' OR international airports.city = '?')

My query returns the correct results and it'll suffice with regards to the homework, but I am wondering basically can JOIN on multiple posts? Wouldso would I construct the WHERE clause therefore it matches the departure and also the destination city/code?

Below is really a "pseudo-query" on which I wish to acheive, however i can't obtain the syntax properly and i'm not sure how you can represent the international airports table for that departures and also the locations:

Choose * FROM plane tickets

INNER JOIN international airports

ON plane tickets.fairport = international airports.code AND plane tickets.tairport = international airports.code

WHERE (international airports.code = 'departureCode' OR international airports.city= 'departureCity')

    AND (international airports.code = 'destinationCode' OR international airports.city = 'destinationCity')

something similar to....

Choose f.*

      ,a1.city as from

      ,a2.city regarding

FROM plane tickets f

INNER JOIN international airports a1

ON f.fairport = a1. code

INNER JOIN international airports a2

ON f.tairport = a2. code

You are able to JOIN with similar table more often than once by providing the became a member of tables an alias, as with the next example:

Choose

    air travel, flt_no, fairport, tairport, depart, arrive, fare

FROM

    plane tickets

INNER JOIN

    international airports from_port ON (from_port.code = plane tickets.fairport)

INNER JOIN

    international airports to_port ON (to_port.code = plane tickets.tairport)

WHERE

    from_port.code = '?' In order to_port.code = '?' OR international airports.city='?'

Observe that the to_port and from_port are aliases for the foremost and second copies from the international airports table.

If you wish to explore both To and from international airports, you will want to join around the International airports table two times - you'll be able to use both to and from tables inside your results set:

Choose Plane tickets., fromAirports., toAirports.* from Plane tickets INNER JOIN International airports fromAirports on Plane tickets.fairport = fromAirports.code INNER JOIN International airports toAirports on Plane tickets.tairport = toAirports.code WHERE ...

if mysql is ok for you personally:

Choose plane tickets.*,

       fromairports.city as fromCity,

       toairports.city as toCity

FROM plane tickets

LEFT JOIN (international airports as fromairports, international airports as toairports)

ON (fromairports.code=plane tickets.fairport AND toairports.code=plane tickets.tairport )

WHERE plane tickets.fairport = '?' OR fromairports.city = '?'

edit: added example to filter the output for code or city