使用連接返回同一列的多個值？

我有兩張桌子

1. 使用者
2. 秩

在Rank表格中，我保存了rank_id和supervisor_id。在Rank表中，我儲存了rank名稱。rank_id和supervisor_id是表中的外鍵Rank。

由於我必須同時選擇rank_id和supervisor_id從同一個表（排名），我如何加入兩個表以獲得結果name，rank name和supervisor？

DDL

CREATE TABLE IF NOT EXISTS `user` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `Name` varchar(90) NOT NULL,
 `rank_id` int(4) NOT NULL,
 `supervisor_id` int(4) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `Name`, `rank_id`, `supervisor_id`) VALUES
(1, 'john', 3, 2),
(2, 'james', 5, 1),
(3, 'geore', 4, 3),
(4, 'aby', 3, 2),
(5, 'john', 2, 1);

CREATE TABLE IF NOT EXISTS `rank` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `rank_name` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `rank`
--

INSERT INTO `rank` (`id`, `rank_name`) VALUES
(1, 'president'),
(2, 'vice president'),
(3, 'prime minister'),
(4, 'cabinet minister'),
(5, 'minister');

預期結果

我需要如下表所示的結果

CREATE TABLE IF NOT EXISTS `result` (
 `id` int(7) NOT NULL,
 `name` varchar(90) NOT NULL,
 `rank` varchar(50) NOT NULL,
 `supervisor` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `result`
--

INSERT INTO `result` (`id`, `name`, `rank`, `supervisor`) VALUES
(4, 'aby', 'prime minister', 'vice president');

您必須將使用者表加入排名表兩次

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.id = 4;

要麼

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.Name = 'aby';

試一試 ！！！

SELECT tbl_trip.trip_id, tbl_trip.location, tbl_member.gender 
FROM (tbl_trip) 
Left JOIN tbl_member ON tbl_member.trip_id = tbl_trip.trip_id

引用自：https://dba.stackexchange.com/questions/71865