CODEWITHSUNDEEP
mysqlsql
Fishingfon
Fishingfon

Reputation: 1034

How to select column twice from the same mysql table?

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

Upvotes: 5

Views: 14509

Answers (5)

djokage
djokage

Reputation: 126

You can simply use the same column multiple times and add for each of them and as and use different name

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

Upvotes: 0

Ajay2707
Ajay2707

Reputation: 5798

Give alias name as

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

Upvotes: 6

Zohar Peled
Zohar Peled

Reputation: 82474

You need to join the pilot table twice in your query. to do that you will have to use an alias for each Pilot table you join.

Upvotes: 1

Bart Friederichs
Bart Friederichs

Reputation: 33511

You have to use table aliases in your join:

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

Upvotes: 1

Oscar Pérez
Oscar Pérez

Reputation: 4397

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

Upvotes: 5

Related Questions