CODEWITHSUNDEEP
mysqlinner-joinbetween
Wilf
Wilf

Reputation: 2315

MySQL join two tables in the same row on different column

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 enter image description here

option_places_db

enter image description here

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: enter image description here

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.

Upvotes: 2

Views: 2067

Answers (2)

koopajah
koopajah

Reputation: 25552

Try this :

SELECT
placeOrg.plc_en AS from_en, 
placeDes.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 AS placeOrg ON time_table_boat.org = placeOrg.plc_id
INNER JOIN option_places_db AS placeDes ON time_table_boat.des= placeDes.plc_id
ORDER BY time_table_boat.org ASC

You want two different names from the table option_places_db so you need to join it twice. First time to retrieve the origin name and second time to retrieve the destination name. You can see you use the same table for origin and destination, you just link them twice et give it a temporary name to differentiate the response you get.

EDIT: corrected the query

Upvotes: 1

aykut
aykut

Reputation: 3094

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

Upvotes: 3

Related Questions