MSSQL Join 2 table

using MSSQL 2008

I got 2 tables

ID   First_Name Last_name
1           Joe           Smith

and another one
Country visited

ID    Country
1        US
1        Canada
1        Mexico

now i want to join the 2 table to display the name of the guy and country visited, is it possible? something like

First_Name  Last_Name  Country_visited
Joe                  Smith       US,Canada,Mexico
Who is Participating?
Without a relationship between two tables you can not join these two tables. Since this is Many to Many relationship you need to have antoher table with the PersonID and CountryID.

Anyway If you want to display all the countires against the user record you can do something like this
SET @MyCountryList = ''
SELECT @MyCountryList = ISNULL(@MyCountryList ,'') + Country + ',' FROM CountryTable

SELECT First_Name ,Last_Name,@MyCountryList AS Country_visited FROM  USERS

Open in new window

Raul77Author Commented:
emoreau : thanks that almost worked but is the result i get

xpr1>, US</Expr1><Expr1>, Canada</Expr1><Expr1>, Mexico</Expr1>

anyway i can get rid of the EXPR tags?

Kaminda : i cant use declare as this is 2008 xpress.
Raul77Author Commented:
emoreau : my bad, it was a mistake on my part, all working now, thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.