CODEWITHSUNDEEP
ms-access-2010
csorive
csorive

Reputation: 98

Show name of a country in three fields

I have a table called Principal with these columns:

And I have another table called Countries with these columns:

I need make a select that shows the Person_ID and the name of the three countries. I have try with INNER JOIN, but always give me the same country in the three fields.

SELECT Principal.Person_ID, Countries.Country_Name AS Country1, Countries.Country_Name AS Country2, Countries.Country_Name AS Country3 
FROM
    (Countries
    INNER JOIN Principal
    ON Countries.ID = Principal.Country1_ID OR Countries.ID = Principal.Country2_ID  OR Countries.ID = Principal.Country3_ID);

What is the right SELECT?

Upvotes: 0

Views: 26

Answers (1)

Richard Taylor-Kenny
Richard Taylor-Kenny

Reputation: 70

I think this will get you want you want. It returns blank when a country does not match.

SELECT Principal.Person_ID
,(SELECT ISNull(Countries.Country_Name,'') FROM Countries WHERE Principal.Country1_ID OR Countries.ID ) AS Country1
,(SELECT ISNull(Countries.Country_Name,'') FROM Countries WHERE Principal.Country2_ID OR Countries.ID ) AS Country2
,(SELECT ISNull(Countries.Country_Name,'') FROM Countries WHERE Principal.Country3_ID OR Countries.ID ) AS Country3;

A merge might work better but I'm not so familiar with them.

Upvotes: 1

Related Questions