0

SQL Fight ID Conflict

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?

13th Sep 2021, 5:02 PM
Jeffrey Tate
22 Answers
+ 6
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.
13th Sep 2021, 5:19 PM
laura
+ 5
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
13th Sep 2021, 5:52 PM
A͢J
A͢J - avatar
+ 2
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.
13th Sep 2021, 5:43 PM
Tibor Santa
Tibor Santa - avatar
+ 2
What are the columns in the two tables. I can't imagine that it makes sense to join on duration and fullname.
13th Sep 2021, 5:46 PM
Simon Sauter
Simon Sauter - avatar
+ 2
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;
13th Sep 2021, 5:53 PM
Jeffrey Tate
+ 2
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.
13th Sep 2021, 8:15 PM
Tibor Santa
Tibor Santa - avatar
+ 1
You haven't defined "p".
13th Sep 2021, 5:08 PM
Simon Sauter
Simon Sauter - avatar
+ 1
Jeffrey Tate You have written AS Pilot instead of AS p
13th Sep 2021, 5:17 PM
A͢J
A͢J - avatar
+ 1
Jeffrey Tate Check LEFT JOIN in Lesson 19.1
13th Sep 2021, 5:57 PM
A͢J
A͢J - avatar
+ 1
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.
14th Sep 2021, 8:03 PM
Tibor Santa
Tibor Santa - avatar
0
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.
13th Sep 2021, 5:37 PM
Jeffrey Tate
0
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;
13th Sep 2021, 5:44 PM
Jeffrey Tate
0
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 ^
13th Sep 2021, 6:50 PM
Jeffrey Tate
0
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
13th Sep 2021, 7:19 PM
Tibor Santa
Tibor Santa - avatar
0
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?
13th Sep 2021, 8:03 PM
Jeffrey Tate
0
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
13th Sep 2021, 8:50 PM
Jeffrey Tate
0
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;
14th Sep 2021, 7:38 PM
Jeffrey Tate
0
;
15th Sep 2021, 5:59 AM
R R
R R - avatar
0
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;
8th Oct 2021, 9:36 PM
Marcos Cortez
Marcos Cortez - avatar
0
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;
30th Nov 2021, 12:29 PM
Lennart Buskens