Sql Join with multiple columns

Siva V

Team,

not sure whether here is the right place or not. but need help on this.

Country

Id Name

1 India

2 UK

3 Canada

4 US

Travel

CountryFrom CountryTo

1 2

2 1

2 3

I need to pull list of countries visited by the user(avoid duplicate countries) and result set should be as follows

Id, CountryVisisted

1 India

2 UK

3 Canada

Please suggest me

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke5y ago
Hello,

If I understand you correctly, you need following query:
declare @country table
(
   [Id]   integer not null identity(1, 1) primary key clustered
  ,[Name] varchar(20)
)

insert into @country values
('India')
,('UK')
,('Canada')
,('US')

declare @travel table
(
   [CountryFrom] integer not null
  ,[CountryTo]   integer not null
)

insert into @travel values
(1, 2)
,(2, 1)
,(2, 3)

select c.[Id], c.[Name]
from @travel t
left outer join @country c on t.[CountryFrom] = c.[Id]
union
select c.[Id], c.[Name]
from @travel t
left outer join @country c on t.[CountryTo] = c.[Id]
Rijwan Ansari5y ago
Would you please share complete table structure? As I see in above table, there is no any user column.

However, to understand join in sql please check below link.

https://www.w3schools.com/sql/sql_join.asp
Regards
Rijwan
Sachin Singh5y ago
there are various ways the easiest one may be
select * from Country where CountryId in (select countryFrom from Travel )
union
select * from  Country where CountryId in (select countryTo from Travel )
Amit Gupta5y ago
You can take advantage of SQL query buider available in SSMS. Press CTRL + SHIFT + Q and a query builder window will open -> Right click -> add tables -> select the columns required and SSMS will build the query for you.

Do let us know if you have any clarification