Sql Join with multiple columns

Question

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

Rijwan Ansari · Answer

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 Singh · Answer

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 Gupta · Answer

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

Nitin Sontakke · Answer

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]