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:
The plane tickets table has got the following posts:
air travel, flt_no, fairport, tairport, depart, arrive, fare
tairport would be the from and to airport terminal codes.
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
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