SQL查询连接多个列

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

我试图从2个表中加入一些数据,但是在几个列上。这是一个例子:

Source table

ID | Desc| AAAA| BBBB|

ID |说明| AAAA | BBBB |

Table2 table

ID | Text| ID1 | ID2 | ID3 |

ID |文| ID1 | ID2 | ID3 |

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

表2中的ID1,ID2和ID3是Source表中的ID

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?

我猜这会是一个连接,但我不能正确的语法...或者我会更好地与联盟?

4 个解决方案

#1

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

如果不是所有的源表都填充在Table2中,这仍然会给你部分结果:

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

#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

#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.

您需要连接三次源表,每个ID一个。你也可以尝试一个联系,看看哪个表现更好。

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.

这是一个糟糕的表设计(它应该被标准化),我建议你现在改变它,如果可能的话。 shoudl bea相关表与每个id在一个单独的记录中,然后你可以加入一次,它会更有效,更容易编写代码,你不必在你需要的那天改变表结构和所有查询ID4。

#4

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

#1