TRYING TO JOIN THESE TABLES but getting Error Code: 1052. Column 'Flight_no' in field list is ambiguous

Question

I am trying to join two tables but getting the "Error Code: 1052. Column 'Flight_no' in field list is ambiguous". Can someone please help me?

Here is my query:

SELECT Flight_no, Airline_code. Lname, Passenger_id
FROM Ticketinfo JOIN  Flights
ON Ticketinfo.Flight_no = Flights.Flight_no;

Here is info on my tables:

CREATE TABLE Flights
(
  Flight_no INT NOT NULL,
  Airline_code char (5) NULL,
  Departure_AirportCode char (5) NOT NULL,
  Departure_date date NOT NULL,
  Arrival_AirportCode char (5) Not NULL,
  Arrival_date date not null,
  Price  decimal (5,2),
  Airline_no INT NOT NULL,
  Route_no varchar (8) NOT NULL,
  PRIMARY KEY (Flight_no),
  FOREIGN KEY (Airline_no) REFERENCES Airlines(Airline_no),
  FOREIGN KEY (Route_no) REFERENCES Routes(Route_no)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE TABLE TicketInfo
(
  Confirmation_no varchar (10) NOT NULL,
  Lname varchar (30) NOT NULL,
  Passenger_id varchar (6) NOT NULL,
  Fare_type varchar (3) NOT NULL,
  Flight_no INT NOT NULL,
   PRIMARY KEY (Confirmation_no),
  FOREIGN KEY (Passenger_id) REFERENCES Passenger(Passenger_id),
  FOREIGN KEY (Flight_no) REFERENCES Flights(Flight_no)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

score 0 · Answer 1 · answered Dec 02 '16 at 20:21

0

It is ambiguous. Flight_No is in both tables.

Prefix a specific table to the field like this:

SELECT Flights.Flight_no, Airline_code. Lname, Passenger_id
FROM Ticketinfo JOIN  Flights
ON Ticketinfo.Flight_no = Flights.Flight_no;

answered Dec 02 '16 at 20:21

Chizzle

1,707
1
17
26

Thanks @ Chizzie - Question is resolved. – zimara1 Dec 02 '16 at 22:26

score 0 · Answer 2 · answered Dec 02 '16 at 20:21

0

Since the Flight_no column appears in both tables, you need to resolve the ambiguity in the SELECT by specifying which table you want the Flight_no column to come from. Since, by virtue of your join, the value is the same in both tables, it really doesn't matter which you choose.

SELECT Ticketinfo.Flight_no, Airline_code. Lname, Passenger_id
FROM Ticketinfo JOIN  Flights
ON Ticketinfo.Flight_no = Flights.Flight_no;

answered Dec 02 '16 at 20:21

Joe Stefanelli

132,803
19
237
235

hey Joe - thanks for the quick response. I did see from a different post that this might be the problem. So I went and modified my code but now I get: Error Code: 1054. Unknown column 'Airline_code.Lname' in 'field list' – zimara1 Dec 02 '16 at 21:37
Nvm Joe it worked now! Many thanks! – zimara1 Dec 02 '16 at 21:56

score 0 · Answer 3 · answered Dec 02 '16 at 20:25

Qualify all column references with either the table name or (my preference) a short table alias. For example:

SELECT f.flight_no
     , f.airline_code
     , t.lname
     , t.passenger_id
  FROM Ticketinfo t
  JOIN Flights f
    ON f.flight_no = t.flight_no

Beyond resolving "ambiguous column" errors, and preventing a working statement from breaking when new columns are added to tables... this also aids future human readers. If a column reference isn't qualified, the reader doesn't know which table that column is in, not without going and looking up the table definitions.

Later, when a column named lname is added to the Flights table, the query won't throw an "ambiguous column" error. That's why we qualify all column references.

@ Spencer...Many thanks! That worked. I am new to this and have been trying to resolve it without knowing what to look for. Thanks also for the explanation. I have save yours and Joe's to my little cheat sheet. — zimara1, Dec 02 '16 at 21:46

TRYING TO JOIN THESE TABLES but getting Error Code: 1052. Column 'Flight_no' in field list is ambiguous

3 Answers3