How to Join Two tables records in mysql

I want to join two tables ,

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

As above tables show , I joint them with the id common in both tables ( from_branch , to_branch )

My Query:

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

how i can get result like

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

Answer

use join with Branch table twice

 select b.from_branch,b.to_branch, 
 bn.name as from_branch_name,
 bn1.name as to_branch_name
 from booking b  join branch bn 
 on b.from_branch=bn.id
 join branch bn1 on b.to_branch=bn1.id

Advertisement

Answer