MSSQL Join 2 table

Posted on 2011-04-26
Last Modified: 2012-05-11
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
Question by:Raul77
    LVL 69

    Accepted Solution

    LVL 9

    Expert Comment

    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


    Author Comment

    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.

    Author Comment

    emoreau : my bad, it was a mistake on my part, all working now, thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now