SQL query to join multiple columns

Question

I am trying to combine some data from two tables, but in multiple columns. here is an example:

Source table

ID | Product Description | AAAA | BBBB |

Table2 table

ID | Text | ID1 | ID2 | ID3 |

where ID1, ID2 and ID3 in Table 2 are identifiers from the Source table

I would like to make a query that gives the results:

Table2.Text,
Source.Desc(ID1),
Source.AAAA(ID1),
Source.Desc(ID2),
Source.AAAA(ID2),
Source.Desc(ID3),
Source.AAAA(ID3)

I would suggest that this will be a union, but I can't get the syntax right ... or am I better off with Union?

Answer 1

2, :

SELECT
    t.Desc, s1.Desc, s1.AAAAA, s2.Desc, s2.AAAAA, s3.Desc, s3.AAAA
    FROM Table2                t
        LEFT OUTER JOIN Source s1 ON t.ID1 = s1.ID
        LEFT OUTER JOIN Source s2 ON t.ID2 = s2.ID
        LEFT OUTER JOIN Source s3 ON t.ID3 = s2.ID
    WHERE t.ID=@YourIDHere

Answer 2

, ? :

SELECT tb.Desc, s1.Desc, s1.AAAAA, s2.Desc, s2.AAAAA, s3.Desc, s3.AAAA
FROM Table2 tb
    INNER JOIN Source s1 ON tb.ID1 = s1.ID
    INNER JOIN Source s2 ON tb.ID2 = s2.ID
    INNER JOIN Source s3 ON tb.ID3 = s2.ID

Answer 3

, . unuion, , .

( ), , . shoudl bea id , , , , ID4.

Answer 4

:

select A.*, coalesce(B1.Text,B2.Text,B3.Text,'') as Text
from Source A
inner join Table2 B1 on B1.ID1=A.ID
inner join Table2 B2 on B2.ID2=A.ID
inner join Table2 B3 on B3.ID3=A.ID