How to select a column twice from the same mysql table?

Question

Lately, I have been doing quite a bit of MySql for uni, and I cannot figure out how to get a field from a table twice in the same expression. My database is as follows:

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 charter date, destination, client information (name, client address, address, telephone number) and the names of pilots (name, surname, surname) of all charters. I managed to get everything, but with only one pilot. I need to also indicate the names of the pilots.

I have a problem with Google, but I can not find anything.

If someone could point me in the right direction, I would be very grateful.

Thanks cheers corey

Answer 1

JOIN .

- :

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

Answer 2

 SELECT a.columname1 AS 1, a.columname1 AS 2
   FROM tablename a

Answer 3

:

SELECT MainPilot.LastName, CoPilot.LastName FROM CHARTER 
LEFT JOIN PILOT MainPilot ON MainPilot.PilotCode=CHARTER.PilotCode
LEFT JOIN PILOT CoPilot ON CoPilot.PilotCode=CHARTER.CoPilotCode

Answer 4

- . -.

Answer 5

as

SELECT column1 as c1, column1 as c2, column1 as c3 FROM TABLE1 WHERE ....