how to display names instead of id in join query of mysql having 2 columns

Question

There is table 'countries' with country_id and country_name. There is another table 'flightticket' with colums country_from,country_to

country_from,country_to store id of countries

i need a slect query that show country name insted of country id

Desired output

India france
germany UAE

i need a proper and correct query for the below query

select country_from
     , country_to 
  from flightticket 
  join countries   
flightticket.country_from = countries.country_id

Answer 1

Try this:

SELECT 
    c_from.country_name AS country_from,
    c_to.country_name AS country_to 
FROM 
    flightticket 
INNER JOIN 
    countries c_from 
ON 
    flightticket.country_from = c_from.country_id
INNER JOIN 
    countries c_to 
ON 
    flightticket.country_to = c_to.country_id

Answer 2

You can specify it, but need to join twice:-

select country_from.country_name AS country_from,
country_to.country_name AS country_to 
from flightticket 
INNER join countries country_from ON flightticket.country_from = country_from.country_id
INNER join countries country_to ON flightticket.country_to = country_to.country_id

One join for each country name you require.

Answer 3

this might helpful

SELECT ft.country_from, ft.country_to FROM flightticket AS ft, JOIN countries AS cuntry ON ft.country_from=cuntry.country_id AND ft.country_to=cuntry.country_to