We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MSSQL Join 2 table

Medium Priority
376 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
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

Author

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

Author

Commented:
emoreau : my bad, it was a mistake on my part, all working now, thank you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.