mysql - 嵌套查询显示 2 个表中的 2 列

Question

表格

客户

custno  |Custname
1       |Melinda
2       |Micheal
4       |Andrew

航类

FlightNo | Day      | Destination | Cost
AF394    |monday    |Cape Town    |2700
FL2X     |Tuesday   |Paris        |5000
HK101    |Sunday    |hong kong    |3500

预订库斯诺 |航类号 |费用

1       | AF394    |2700
1       |FL2X      |5000
1       |HK101     |3500
2       |HK101     |3500
6       |AF394     |2700
7       |AF394     |2700

问题显示名为 Melinda 的客户的预订详细信息，包括 CUSTNO,FKIGHTNO,DESTINATIN 和 DAY。

我是MySQL的初学者，希望有人能帮我解决这个问题!!

Answer 1

--with joins
select r1.custno,r1.flightno,f1.destination,f1.day
from customer c1 join reservation r1 on c1.custno = r1.custno
                 join flight f1 on r1.flightno = f1.flightno
where c1.custname = 'Melinda'

--with nested queries
select (select custno from customer where custname = 'Melinda') custno,
        f1.flightno,f1.destination,f1.day
from flight f1 
where exists (
               select 1 
               from reservation 
               where flightno = f1.flightno and 
                     custno = (
                                select custno
                                from customer 
                                where custname = 'Melinda'
                               )
              )