[FIXED] How to replace multiple columns in one SQL query?

Issue

I have 2 tables: country and trip.

A trip can have up to 3 country codes.

country table

country_code	country_name
FRA	FRANCE
IRL	IRELAND
JPN	JAPAN
MAR	MOROCCO
NZL	NEW ZEALAND

trip table

trip_id	country_code	country_code2	country_code3
1	FRA	IRL	JPN
2	MAR	NZL

My goal is to have country names displayed on the trip table instead of country codes.

I succeed to have only 1 country code replaced, thanks to the left join clause. I would like to have up to 3 country names displayed per row.

SELECT trip_id, country_name
FROM trip
LEFT JOIN country ON country_code = country_name

The actual output of the trip table:

trip_id	country_name
1	FRANCE
2	MOROCCO

Is there a way to replace each country code with its corresponding country name?

The EXPECTED output of the query from the trip table:

trip_id	country_name	country_name2	country_name3
1	FRANCE	IRELAND	JAPAN
2	MOROCCO	NEW ZEALAND

Thank you!

Solution

The cleanest way of accomplishing this query is using subqueries:

SELECT t.trip_id, 
       (SELECT country_name FROM country WHERE country_code =  t.country_code) "c1",
       (SELECT country_name FROM country WHERE country_code = t.countty_code2) "c2",
       (SELECT country_name FROM country WHERE country_code = t.country_code3) "c3"
FROM trip t

Answered By - Gonnen Daube

Answer Checked By - Mary Flores (PHPFixing Volunteer)