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(10…

Accepted Answer

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
```

How to select column twice from the same mysql table?

Solution

Problem