php - mysql中如何连接两个表的记录

Question

我想连接两个表，

booking table
id from_branch to_branch
1     2            4
2     3            4

Branch Table
 id    name
 1     pune
 2     mumbai
 3     nanded
 4     parbhani
 5     aurangabad

如上表所示，我将它们与两个表中通用的 id(from_branch、to_branch)连接起来

我的查询:

SELECT booking.id,
       booking.from_branch,
       booking.to_branch,
       branch.branch_id,
       branch.branch_name AS "FROM",
       branch.branch_name AS "TO"
FROM   booking
       JOIN branch
         ON booking.from_branch = branch.branch_id
WHERE  booking.from_branch = branch.branch_id
        OR booking.to_branch = branch.branch_id 

我怎样才能得到像

这样的结果

id  from_branch to_branch     FROM      TO
     1     2           4         Mumbai   Parbhani
     2     3           4         Nanded   Parbhani

Answer 1

左连接第二个表两次

SELECT a.*, b.name as 'FROM', c.name as 'TO'
FROM booking a
LEFT JOIN Branch b ON a.from_branch = b.id 
LEFT JOIN Branch c ON a.to_branch = c.id