MySQL join two tables in the same row on different column

Question

I'm going to join two table with the same destination but different origin value. I'm sure I'm not good in explain in words. But hopefully you're going to get what I want by my code below:

MySQL

SELECT 
option_places_db.plc_en AS from_en, 
option_places_db.plc_th AS to_en, 
concat( time_start_hr, ':', time_start_min ) AS time1, 
concat( time_end_hr, ':', time_end_min ) AS time2, 
price_adult, 
price_child
FROM `time_table_boat`
INNER JOIN option_places_db ON time_table_boat.org = option_places_db.plc_id
ORDER BY time_table_boat.org ASC

time_table_boat

option_places_db

According to time_table_boat table. I expect :

from_en    to_en     time1    time2    price_adult    price_child
Aonang     Phi Phi   09:00    11:00    450            350
Phi Phi    Aonang    15:30    17:00    450            350

But from running mySQL it turned something like this instead:

The column 'to_en' is not match with the time_table_boat.des=option_places_db (=3) as it should be. I've tried to add more line of inner join but it comes up with error of the same destination table name.

Answer 1

Try this

SELECT 
    org_places.plc_en AS from_en, 
    des_places.plc_en AS to_en, 
    CONCAT( time_start_hr, ':', time_start_min ) AS time1, 
    CONCAT( time_end_hr, ':', time_end_min ) AS time2, 
    price_adult, 
    price_child
FROM `time_table_boat`
INNER JOIN 
    option_places_db AS org_places ON time_table_boat.org = org_places.plc_id
INNER JOIN 
    option_places_db AS des_places ON time_table_boat.des = des_places.plc_id
ORDER BY time_table_boat.org ASC