[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

SQL multiple values JOIN?

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.
0
simplyfemales
Asked:
simplyfemales
  • 4
  • 2
2 Solutions
 
Ephraim WangoyaCommented:
You are not clear, are Code1, Code2, and Code3 columns of Table1 or are they rows of the table
0
 
Ephraim WangoyaCommented:
If rows the simply

select Codes, Description
from Table1
inner join Table2 on Table1.Column1 = Table2.Codes
0
 
simplyfemalesAuthor Commented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Ephraim WangoyaCommented:
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
0
 
Ephraim WangoyaCommented:

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
0
 
simplyfemalesAuthor Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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