Searching connected flights from my airline database for more than two intermediate routes
I am currently working on a training mini-project for booking an air ticket between source and destination.
My relational database table for Flight_table
(flight_id + flight_date)
is my primary key.
seats available = 0
abc-> pqr travel, then I have to look for a connecting flight. For one intermediate stop, I thought of a simple concatenation of myself like
SELECT a.flight_name , a.f_source, a.f_dest, b.flight_name, b.f_source, b.f_dest FROM FLIGHT_TABLE a, FLIGHT_TABLE b WHERE a.source = 'abc' AND a.f_dest = b.f_source AND b.f_dest = 'pqr' AND (some date related condition)
Can anyone help me write an efficient query if there are two or more intermediate stops (flights)
Thanks in advance.
source to share