CODEWITHSUNDEEP
sqlleft-join
stan
stan

Reputation: 49

How to replace multiple columns in one SQL query?

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!

Upvotes: 0

Views: 180

Answers (2)

Gonnen Daube
Gonnen Daube

Reputation: 317

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

Upvotes: 1

nbk
nbk

Reputation: 49385

You could add two more joins

SELECT trip_id, c1.country_name, c2.country_name, c3.country_name
FROM trip t
left join 
country c1
on t.country_code = c1.country_code
left join 
country c2
on t.country_code2 = c2.country_code
left join 
country c3
on t.country_code3 = c3.country_code

Upvotes: 2

Related Questions