SQL Server join two columns both ID of a table with another table's primary Key Column

Question

I was wondering if there is an easy way of joining these two tables.

Table1

Name FromCountryID ToCountryID
------------------------------
sam         1          2    
lee         3          4
john        2          1

Table2:

CountryID   CountryName
1           USA
2           UK
3           Canada
4           Nepal

Possible duplicate of [Joining multiple tables in SQL](http://stackoverflow.com/questions/15987321/joining-multiple-tables-in-sql) — Beloo, Dec 27 '16 at 08:32
Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — rob2universe, Dec 27 '16 at 08:43

juergen d · Accepted Answer · 2016-12-27T08:30:41.963

1

You need to join the same table twice with different alias names

select t1.name, 
       fromTab.countryName as FromCountry,
       toTab.countryName as ToCountry
from table1 t1
left join table2 fromTab on fromTab.countryId = t1.fromCountryId
left join table2 toTab on toTab.countryId = t1.toCountryId

edited Dec 27 '16 at 08:30

answered Dec 27 '16 at 08:23

juergen d

201,996
37
293
362

If just in case FromCountryID or ToCountryID might be null.what would be the correct Query??? – Sandeep Maharjan Dec 27 '16 at 08:29

score 0 · Answer 2 · answered Dec 27 '16 at 08:44

0

 SELECT * FROM  Table1 INNER JOIN Table2 ON Table2.CountryID = Table1.FromCountryID

answered Dec 27 '16 at 08:44

Gayathri Rajan

369
1
6
18

Thank for your suggestion.Maybe my question was not that clear but @juergen answer was correct in my case. – Sandeep Maharjan Dec 27 '16 at 08:54

SQL Server join two columns both ID of a table with another table's primary Key Column

2 Answers2