多个列连接到单个列SQL Server 2008R2

Question

我有两个相互关联的表，表1和表2。

Table1:

ID int IDENTITY Primary Key
Col1 varchar(15)

表:

ID int IDENTITY Primary Key
CenterID int
SatelliteID int
Category varchar(15)

Table2.CenterID和Table2.SatelliteID引用Table1.ID(外键)。

问题是，我想检索标准为Table2.CenterID、Table2.SatelliteID和Table2.Category的Table1数据。

我应该如何连接引用相同键的2列?

我的代码

SELECT ta1.* FROM Table1 ta1
INNER JOIN Table2 ta2a ON ta2a.CenterID = ta1.ID
INNER JOIN Table2 ta2b ON ta2b.SatelliteID = ta1.ID
WHERE ....

Answer 1

你可以只加入Table2两次，一次用CenterID，第二次用卫星id:

应该这样做:

SELECT t1.*, t2a.*, t2b.* FROM Table1
LEFT JOIN Table2 t2a ON t2a.CenterID = t1.id
LEFT JOIN Table2 t2b ON t2b.SateliteID = t1.id

Answer 2

你可以这样做:

select 
    table2.id,
    t1.Col1 as Center, 
    t2.Col1 as Satellite,
    table2.category
from table2 left join 
table1 t1 
on t1.ID= table2.centerid
left join table1 t2
on t1.ID= table2.satelliteid
--- add where criteria here

Answer 3

您必须与表2进行多次连接

SELECT * 
FROM TableB AS b 
INNER JOIN TableA AS a1 ON b.CenterID  = a1.Id  
LEFT OUTER JOIN TableA AS a2 ON b.SatelliteID = a2.Id 

添加INNER JOIN或LEFT OUTER JOIN，这将根据您的要求。