How to select column twice from the same mysql table?

Question

I've been doing quite a bit of MySql lately for uni, and i cant seem to figure out how to get a field from a table twice in the same statement. My database is this:

drop database if exists AIRLINE;
create database AIRLINE;
use AIRLINE;



CREATE TABLE AIRCRAFT
(
    AircraftNo INT(20) NOT NULL,
    AircraftType VARCHAR(100) NOT NULL, 
    FuelBurn VARCHAR(100) NOT NULL, 
    Airspeed VARCHAR(100) NULL, 
    LastInspection DATE NULL, 
    TotalFlyingTime INT(50) NOT NULL, 
    TotalTimeLeftEngine INT(50) NULL, 
    TotalTimeRightEngine INT(50) NULL,

    PRIMARY KEY (AircraftNo)
);

CREATE TABLE PILOT
(
    PilotCode INT(20) NOT NULL, 
    LastName VARCHAR(100) NOT NULL, 
    FirstName VARCHAR(100) NOT NULL, 
    MiddleInitial VARCHAR(50) NULL, 
    HiredDate DATE NULL, 
    BasePay VARCHAR(50) NULL, 
    Dependents VARCHAR(100) NULL, 
    License INT(50) NOT NULL,
    TotalHours INT(50) NOT NULL,

    PRIMARY KEY (PilotCode)
);

CREATE TABLE CUSTOMER
(
    CustomerNo INT(20) NOT NULL,
    Name VARCHAR(100) NOT NULL, 
    Contact INT(50) NOT NULL, 
    Phone INT(50) NOT NULL, 
    Street VARCHAR(100) NULL, 
    Suburb VARCHAR(100) NULL, 
    State VARCHAR(100) NULL, 
    Postcode INT(20) NULL, 
    Balance INT(50) NULL,

    PRIMARY KEY (CustomerNo)

);

CREATE TABLE CHARTER
(
    TripTicket INT(50) NOT NULL AUTO_INCREMENT, 
    CharterDate DATE NOT NULL, 
    PilotCode INT(20) NOT NULL, 
    CopilotCode INT(20) NULL, 
    AircraftNo INT(20) NOT NULL, 
    Destination VARCHAR(100) NOT NULL, 
    Distance INT(20) NULL, 
    HoursFlow INT(20) NULL, 
    HoursWating INT(20) NULL, 
    Fuel INT(20) NULL, 
    Oil INT(20) NULL, 
    CustomerNo INT(20) NOT NULL,

    PRIMARY KEY (TripTicket),

    FOREIGN KEY(PilotCode) REFERENCES PILOT(PilotCode),
    FOREIGN KEY(CopilotCode) REFERENCES PILOT(PilotCode),
    FOREIGN KEY(AircraftNo) REFERENCES AIRCRAFT(AircraftNo),
    FOREIGN KEY(CustomerNo) REFERENCES CUSTOMER(CustomerNo)
);

My goal is to list the charterdate, destination, customer details (name, customerNo, address, phone), and pilot names (firstname, middleinitial, lastname) of all charters. I have managed to get everything, but only with one pilot. I need to list both pilot names however.

I have googled my problem, but i cant seem to find anything.

If someone could please point me in the right direction, i would be hugely grateful.

Thanks Cheers Corey

Answer 1

You just need to JOIN the table twice with different aliases.

Something like:

SELECT p1.lastname, p2.lastname, /* other fields */
  FROM CHARTER c
  JOIN PILOT p1 ON p1.PilotCode = c.PilotCode
  JOIN PILOT p2 on p2.PilotCode = c.CoPilotCode