SQL Fight ID Conflict

Question

So I am stuck on this one in the SQL Course, any help is appreciated:

Write a query to output the flight ID, full name of the pilot who is responsible for the flight, the country where the flight will land, and the duration of the flight (flight_id, fullname, landing_country, duration).

I wrote it out like this:

Select f.ID, p.fullname, f.landing_country, f.duration
From Flights AS f, Pilots AS Pilot
Where f.ID,f.landing_country, f.duration=p.fullname
Order by f.ID;

Getting this message:

ERROR: syntax error at or near ","
LINE 3: Where f.ID, f.landing_country, f.duration=p.fullname
^

Can someone please help me point out my mistake?

Answer 1

Look up how to do a join query. I see a couple of issues..... And if you alias the table as pilot.. you can't call it p.

Answer 2

Jeffrey Tate

Also Join is wrong. There should be

FROM Flights AS f
LEFT JOIN Pilots AS p
ON p.flight_id = f.id

Join should be on id not duration

Answer 3

Your WHERE condition is still wrong.
You need to provide a way for the SQL to connect the two tables.
I assume there would be a pilot_id in the flights table, or a flight_id in the pilots table, so you need to write this in your where condition how the two tables can be combined.

Answer 4

What are the columns in the two tables.
I can't imagine that it makes sense to join on duration and fullname.

Answer 5

Apologies,

I am very new at this.

I have it written as this now:

Select f.ID, p.fullname, f.landing_country, f.duration
From Flights AS f, Pilots AS p
Where f.ID=p.pilot_ID
Order by f.ID;

Answer 6

Your join condition is still wrong.

The pilots table contains two identifiers. The pilot_id is not relevant for this task. The flight_id is the foreign key in this table and you need to use this to connect the pilot to the flight.

ON flights.id = pilots.flight_id

The other problem is the fields you are selecting.
SELECT * won't be enough for the task, you need to list the names of the fields, in the same order as in the Expected Output.

Answer 7

You haven't defined "p".

Answer 8

Jeffrey Tate

You have written AS Pilot instead of AS p

Answer 9

Jeffrey Tate

Check LEFT JOIN in Lesson 19.1

Answer 10

The id column in the flights table, is the same as the flight_id column in the pilots table. Because that is how you joined the two tables. The exercise wants flight_id. So you can just change the query to

SELECT pilots.flight_id,...

(the rest is the same)
And it should work.

Answer 11

Thanks.

So far I have this:

Select f.ID, p.fullname, f.landing_country, f.duration
From Flights AS f, Pilots AS p
Where f.ID = 'p.fullname'
Order by f.ID;

But still getting errors.

Answer 12

Also this give me errors:

Select f.ID, p.fullname, f.landing_country, f.duration
From Flights AS f, Pilots AS p
Where f.ID,f.landing_country, f.duration=p.fullname
Order by f.ID;

Answer 13

So I read through the Join statement again

And I rewrote it as such:

Select pilot_id.pilots,Id.Flights
From pilots Left Join Flights
ON pilots.pilot_id=Flights.Id
Order By ID;

This is my error now:

ERROR: missing FROM-clause entry for table "pilot_id"
LINE 1: Select pilot_id.pilots,Id.Flights
^

Answer 14

When you select a field, the table name (or alias) should be on the left side of the dot, the column name on the right side.

pilots.pilot_id

Answer 15

I am getting closer now I think:

Select*From pilots Left Outer Join Flights
ON flights.id=pilots.pilot_id
Order By flights.id;

Your Output
pilot_id,fullname,nationality,flight_id,id,duration,landing_country
4,Brooklyn Austin,USA,3,4,2h,Mexico
2,Rayan Gomez,England,4,2,3h,USA
3,Omar Wallace,France,1,3,3h,Russia
1,John Ritchson,USA,2,1,4h,Germany

Expected Output
flight_id,fullname,landing_country,duration
1,Omar Wallace,Germany,4h
2,John Ritchson,USA,3h
3,Brooklyn Austin,Russia,3h
4,Rayan Gomez,Mexico,2h

Can someone help me point out what I might be missing?

Answer 16

Okay I think I almost have it.

Select flights.id,pilots.fullname,flights.landing_country,flights.duration From pilots Left Outer Join Flights
ON flights.id=pilots.flight_id
Order By flight_id;

Your Output
id,fullname,landing_country,duration
1,Omar Wallace,Germany,4h
2,John Ritchson,USA,3h
3,Brooklyn Austin,Russia,3h
4,Rayan Gomez,Mexico,2h
Expected Output
flight_id,fullname,landing_country,duration
1,Omar Wallace,Germany,4h
2,John Ritchson,USA,3h
3,Brooklyn Austin,Russia,3h
4,Rayan Gomez,Mexico,2h

Answer 17

Okay I got this far, but it is not giving me the flight_ID just ID. I am not sure what is missing

Select flights.id,pilots.fullname,flights.landing_country,flights.duration From pilots Left Outer Join Flights
ON flights.id=pilots.flight_id
Order By flight_id;

Answer 18

;

Answer 19

SELECT f.id AS flight_id, p.fullname, f.landing_country, f.duration
FROM flights AS f, pilots AS p
WHERE f.id = p.flight_id
ORDER BY f.id;

Answer 20

This is the answer:

SELECT f.id AS flight_id, p.fullname, f.landing_country, f.duration
FROM flights AS f, pilots AS p
WHERE f.id = p.flight_id
ORDER BY f.id;