CODEWITHSUNDEEP
sqlinner-join
BMN
BMN

Reputation: 1

Selecting full names of pairs of ISO-coded countries

Suppose I have got two tables, one of which is named A and contains two columns : country1 (as an ISO code), country2 (as an ISO code) whilst the other one is named B and contains two columns : code (ISO code of country) and name (full name of country).

I would like to list all pairs of countries that occur in table A using their full names, rather than their ISO codes.

If I had to get the full name of every country that occurs in the first column of table A, I would write te following:

SELECT name AS name1
FROM B
    INNER JOIN A ON B.code = A.country1

However, I am struggling to find out how to get both columns with full names.

Upvotes: 0

Views: 146

Answers (2)

sjw
sjw

Reputation: 6543

You should be able to join the code column from B on both the country1 and country2 columns from A. You just need to make sure you alias it differently on each join.

SELECT A.country1, A.country2, B1.name AS name1, B2.name as name2
FROM A
JOIN B AS B1 ON B1.code = A.country1
JOIN B AS B2 ON B2.code = A.country2

Upvotes: 1

Ven
Ven

Reputation: 2014

Join should always be on same column type from both tables. Isnull will work for you, If country codes are stored in different columns in TableA

SELECT name AS name1
FROM B
    INNER JOIN A ON B.code = isnull(A.country1, a.Country2)

or Use case statement

   SELECT name AS name1
    FROM B
        INNER JOIN A ON B.code = case when A.country1 is null then A.country2 
                                   else A.country1 end

Upvotes: 0

Related Questions