SQL Server - How to Map values to Main Table

Question

I have this main table:

tblMain
-----------------------------------------------
|ClientNo|Country1|Country2|Country3|Agreement|
|--------+--------+--------+--------+---------|
|111123  |SG      |TH      |PH      |OA1      |
|111222  |PH      |MY      |JP      |OA2      |
|323211  |MY      |SG      |PH      |OA3      |
-----------------------------------------------

and I have these mapping tables:

tblCountry
-------------------------
|CountryCode|CountryName|
|-----------+-----------|
|SG         |Singapore  |
|MY         |Malaysia   |
|PH         |Philippines|
|TH         |Thailand   |
|JP         |Japan      |
-------------------------

tblAgreement
--------------------
|ACode|ADescription|
|-----+------------|
|OA1  |Agreement1  |
|OA2  |Agreement2  |
|OA3  |Agreement3  |
--------------------

What I need to achieve is to just return the main table but instead of Codes, I need the mapped name/description. So for my example, it should look like this:

-------------------------------------------------------
|ClientNo|Country1   |Country2 |Country3   |Agreement |
|--------+-----------+---------+-----------+--------- |
|111123  |Singapore  |Thailand |Philippines|Agreement1|
|111222  |Philippines|Malaysia |Japan      |Agreement2|
|323211  |Malaysia   |Singapore|Philippines|Agreement3|
-------------------------------------------------------

Can you please help me how to solve this? Thank you in advance

Answer 1

Its good to use join rather than sub query in select statement. Please see the below solution.

Select 
   Main.ClientNo As ClientNo, 
   CountryA.CountryName, 
   CountryB.CountryName, 
   CountryC.CountryName, 
   A.ADescription As Aggrement
From tblMain AS Main
INNER JOIN tblCountry AS CountryA ON CountryA.CountryCode = Main.Country1
INNER JOIN tblCountry AS CountryB ON CountryB.CountryCode = Main.Country2
INNER JOIN tblCountry AS CountryC ON CountryC.CountryCode = Main.Country3
INNER JOIN tblAgreement A ON Main.Agreement = A.Acode;