SQL query to join several columns

Question

I'm trying to join some data together from 2 tables, but on several columns. here's an example:

Source table

ID | Desc| AAAA| BBBB|

Table2 table

ID | Text| ID1 | ID2 | ID3 |

where ID1, ID2 and ID3 in Table2 are ID's from the Source table

I'd like to do a query which yields the results:

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

I'd guess this would be a join, but i can't get the syntax right... or would I be better off with a Union?

Answer 1

Three joins should do the trick:

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

Answer 2

You could just use multiple joins, couldn't you? For example:

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

You need to join to the source table three times, one for each ID. You could also try a unuion to see which performs better.

This is a bad table design (it should be normalized) and I would suggest you change it now if at all possible. There shoudl bea related table with each id in a separate record, then you could join once and it would be much more efficient and far easier to write code against and you wouldn't have to change the table structure and allthe queries the day you need ID4.

Answer 4

If not all the Source tables are populated in the Table2, this will still give you partial results:

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