Multiple Columns in one table referencing back to

Question

I have a table table_One which has Multiple columns(Column1, Column2, Column3, Column4.....) both references (Contains PK values for Table_two) to another table Table_Two. Is there any efficient way getting joining these two tables rather than joining Table_one back to table_Two Multiple Times.br/>

The Structure of the Two tables and the Desired Result Set is as follows. Table_One

Answer 1

Select Table_One.PrimaryKey,
       T2_Column1.ColumnA As Column1,
       T2_Column2.ColumnA As Column2
From   Table_One
       Inner Join Table_Two As T2_Column1
         On Table_One.Column1 = T2_Column1.ID
       Inner Join Table_Two As T2_Column2
         On Table_One.Column2 = T2_Column2.Id

Basically, you join to table 2 twice. When you do this, you MUST alias at least one of them so that SQL doesn't get confused. As a matter of practice, it's usually best to alias both of them so that when you read this code again in 6 months, it will be easier to understand.

Answer 2

Have you tried aliasing your table2 table and joining it twice to Table_One like below?

SELECT
    t1.PrimaryKey,
    c1.ColumnA AS Column1,
    c2.ColumnA AS Column2
FROM Table_One t1
JOIN Table_two c1 ON t1.Column1 = c1.ID
JOIN Table_two c2 ON t1.Column2 = c2.ID;

Answer 3

Following solution (SQLFiddle) reads the rows from the second table just one time:

SET STATISTICS IO ON;
...
PRINT 'Test #1'
SELECT  *
FROM
(
    SELECT  ca.PrimaryKey, ca.[Type], y.ColumnA
    FROM    @Table1 x
    UNPIVOT( Value FOR [Type] IN ([Column1], [Column2]) ) ca
    INNER MERGE /*HASH*/ JOIN @Table2 y ON ca.Value = y.ID
) src
PIVOT( MAX(src.ColumnA) FOR src.[Type] IN ([Column1], [Column2]) ) pvt
PRINT 'End of Test #1'

Results:

Test #1
PrimaryKey Column1   Column2
---------- --------- -------
1          ALPHA     CHARLIE
2          BETA      DELTA
3          CHARLIE   ALPHA
4          DELTA     CHARLIE
5          ALPHA     DELTA
6          CHARLIE   ALPHA
7          ALPHA     DELTA
8          DELTA     CHARLIE

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#65B6F546'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#61E66462'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

End of Test #1