SUB-QUERY PROBLEM ---- PLEASE HELP URGENTLY

[Aloys Ochola]

Here is the question and the codes for creating the tables

Qn : In case of emergency pilots need to know nearby airports that they could divert to. Write a query that returns a list of all flight numbers, their intended destination and other airports in the same city that they could land at if required.

DROP TABLE IF EXISTS `fly-four-less`.`airports`;
CREATE TABLE `fly-four-less`.`airports` (
`IATA_CODE` varchar(3) NOT NULL default '',
`NAME` varchar(30) default NULL,
`CTY_IATA_CODE` varchar(3) default NULL,
`UTC_OFFSET` decimal(2,0) default NULL,
`ICAO_CODE` varchar(4) default NULL,
`ROUTE_SWARE_APT_NME` varchar(100) default NULL,
PRIMARY KEY (`IATA_CODE`),
KEY `FK_airports_1` (`CTY_IATA_CODE`),
CONSTRAINT `airports_ibfk_1` FOREIGN KEY (`CTY_IATA_CODE`) REFERENCES `cities` (`IATA_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `fly-four-less`.`cities`;
CREATE TABLE `fly-four-less`.`cities` (
`IATA_CODE` varchar(3) NOT NULL default '',
`COU_IATA_CODE` varchar(2) default NULL,
`NAME` varchar(40) default NULL,
PRIMARY KEY (`IATA_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `fly-four-less`.`flight_profiles`;
CREATE TABLE `fly-four-less`.`flight_profiles` (
`FLP_ID` decimal(8,0) NOT NULL default '0',
`FLT_NUMB` decimal(4,0) default NULL,
`SEATS` int(11) default NULL,
`PRICE_CODE` varchar(50) default NULL,
`from` varchar(50) default NULL,
`to` varchar(50) default NULL,
`MON` varchar(1) default NULL,
`TUE` varchar(1) default NULL,
`NEXT_DAY` int(11) default NULL,
`WED` varchar(1) default NULL,
`THU` varchar(1) default NULL,
`FRI` varchar(1) default NULL,
`SAT` varchar(1) default NULL,
`SUN` varchar(1) default NULL,
`depart` datetime default NULL,
`arrive` datetime default NULL,
PRIMARY KEY (`FLP_ID`),
UNIQUE KEY `FLT_NUMB` (`FLT_NUMB`),
KEY `FK_flight_profiles_1` (`from`),
KEY `FK_flight_profiles_2` (`to`),
CONSTRAINT `flight_profiles_ibfk_1` FOREIGN KEY (`from`) REFERENCES `airports` (`IATA_CODE`),
CONSTRAINT `flight_profiles_ibfk_2` FOREIGN KEY (`to`) REFERENCES `airports` (`IATA_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then came up with the following query which does not exactly portray the contents required by the question, any help will be highly appreciated.

SELECT F.FLT_NUMB, A.NAME AS DEST_AIRPORT,
C.NAME AS CITY_LOCATION, F.ARRIVE AS ARRIVAL_TIME
FROM FLIGHT_PROFILES F INNER JOIN AIRPORTS A
ON F.TO=A.IATA_CODE
INNER JOIN CITIES C
ON A.CTY_IATA_CODE=C.IATA_CODE
WHERE F.TO IN(
SELECT A.IATA_CODE FROM AIRPORTS A
INNER JOIN CITIES C
ON A.CTY_IATA_CODE=C.IATA_CODE)
ORDER BY C.NAME ASC;

Thanks in advance ---
Aloys