• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

MSSQL Join 2 table

using MSSQL 2008

I got 2 tables
USERS

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
0
Raul77
Asked:
Raul77
  • 2
1 Solution
 
kamindaCommented:
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
DECLARE @MyCountryList VARCHAR(MAX)
SET @MyCountryList = ''
SELECT @MyCountryList = ISNULL(@MyCountryList ,'') + Country + ',' FROM CountryTable

SELECT First_Name ,Last_Name,@MyCountryList AS Country_visited FROM  USERS

Open in new window

0
 
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.
Thanks
0
 
Raul77Author Commented:
emoreau : my bad, it was a mistake on my part, all working now, thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now