sql连接两个表并得到相应的答案

zpgglvta 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(229)

这是我的小提琴
我有一张主桌

CREATE TABLE master (id int, name varchar(25), origin int, destination int);
INSERT INTO master (id, name, origin, destination) VALUES (1, 'Alpha', 1,2);
INSERT INTO master (id, name, origin, destination) VALUES (2, 'Beta', 3,4);

还有机场的table

CREATE TABLE airport (id int, name varchar(25));
INSERT INTO airport (id, name) VALUES (1, 'Chennai');
INSERT INTO airport (id, name) VALUES (2, 'Delhi');
INSERT INTO airport (id, name) VALUES (3, 'Goa');
INSERT INTO airport (id, name) VALUES (4, 'Mumbai');

我尝试使用这个sql查询

select master.id, master.name, master.origin,master.destination from  master
INNER JOIN airport ON master.origin=airport.id

但我只得到这个答案

id  name    origin  destination
1   Alpha   1   2
2   Beta    3   4

我怎么能这样

id  name    origin  destination
1   Alpha   Chennai Delhi
2   Beta    Goa     Mumbai

sql mysql Join Database

来源：https://stackoverflow.com/questions/51464723/sql-join-2-tables-and-get-respective-answers

2条答案

按热度按时间

jmo0nnb31#

你可以试试这个查询，希望你能得到你想要的答案。
查询：

SELECT master.id,
       master.name,
       master.destination                       AS destination,
       (SELECT airport.name
        FROM   airport
        WHERE  airport.id = master.origin)      AS origin,
       (SELECT .airport.name
        FROM   airport
        WHERE  airport.id = master.destination) AS destination
FROM   master

赞(0）回复(0）举报 2021-06-21

ulydmbyx2#

您需要两个连接：

select m.id, m.name, ao.name as origin, ad.name as destination
from master m join
     airport ao 
     on m.origin = ao.id join
     airport ad
     on m.destination = ad.id;

注意使用表别名来区分这两个表。这些也有助于更容易地编写和读取查询。

赞(0）回复(0）举报 2021-06-21

我来回答

sql连接两个表并得到相应的答案

2条答案

相关问题

热门标签

最新问答