SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1
(
name varchar(100),nearestairport varchar(100),destinationaiport varchar(100)
);
create table table2
(
flight varchar(100),departureairport varchar(100),arrivaleairport varchar(100)
);
insert into table1(name ,nearestairport ,destinationaiport )
values('Jason','JFK','CLT'),('Robert','LAG','RDU'),('Wel','CAK','CLT'),
('Adam','RDU','JFK')
4 rows affected

insert into table2(flight ,departureairport ,arrivaleairport )
values('A1234','JFK','CLT'),('A1235','JFK','RDU'),('A1255','RDU','CLT'),
('A1255','RDU','JFK'),('A1237','LSG','RDU'),('A1259','CAK','JFK')
6 rows affected

select X.name,x.nearestairport,case when X.destinationaiport=X.arrivaleairport then ''
else X.arrivaleairport end as stop1,X.destinationaiport from
(select name, a.nearestairport, a.destinationaiport,b.arrivaleairport
from table1 a left join table2 b
on a.nearestairport=b.departureairport)X
left join table2 c 
on X.destinationaiport=c.arrivaleairport and X.nearestairport=c.departureairport


name
nearestairport
stop1
destinationaiport


Jason
JFK

CLT

Jason
JFK
RDU
CLT

Robert
LAG
null
RDU

Wel
CAK
JFK
CLT

Adam
RDU
CLT
JFK

Adam
RDU

JFK

name	nearestairport	stop1	destinationaiport
Jason	JFK		CLT
Jason	JFK	RDU	CLT
Robert	LAG	null	RDU
Wel	CAK	JFK	CLT
Adam	RDU	CLT	JFK
Adam	RDU		JFK