SQL multiple values JOIN?

Posted on 2011-04-22
Last Modified: 2012-05-11
I have two tables where I want to have the values of column 1, 2 and 3 referenced against column 1 of Table 2 and return a value from column2 of table 2 for each value in table 1.  See below

Table 1

Code1, Code2, Code3

Table 2

Codes, Descriptions

Desired output

Code1, looked up description for code1, Code2, looked up description for code2, Code3, lookedup up description for code3

Any help is greatly appreciated.
Question by:simplyfemales
    LVL 32

    Expert Comment

    You are not clear, are Code1, Code2, and Code3 columns of Table1 or are they rows of the table
    LVL 32

    Expert Comment

    If rows the simply

    select Codes, Description
    from Table1
    inner join Table2 on Table1.Column1 = Table2.Codes

    Author Comment

    My apologies for being vague.

    There are roughly 125 unique codes available in Table 2 (with respective descriptions).  On table 1, I have roughly 500k rows of data where each field in column1 (Code1), column2 (Code2) and column3 (Code3) could be any one of the 125 codes.  I would like to have their respective descriptions matching each code.

    Hopefully that helps.
    LVL 32

    Accepted Solution

    if columns, you can do this

    select (select Description from Table2 where Codes = Code1) [description 1],
              (select Description from Table2 where Codes = Code2) [description 2],
              (select Description from Table2 where Codes = Code3) [description 3]
    from Table1
    LVL 32

    Assisted Solution


    Or you can do it this way (I'm assuming the column names are Code1, Code2, and Code3 Replace with the correct column name if I'm not correct)

    select A.Description [description 1], B.Description [description 2], C.Description [description 3]
    from Table1
    left join Table2 A on A.Codes = Table1.Code1
    left join Table2 B on B.Codes = Table1.Code2
    left join Table2 C on C.Codes = Table1.Code3

    Author Closing Comment

    I modified your suggestion of course to fit my tables.  FYI suggestion one I marked (your earlier one) was twice as fast as suggestion 2.  Both worked great though.  Thanks for the quick responses.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    755 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

    21 Experts available now in Live!

    Get 1:1 Help Now