SQL多个内部连接在两个表之间

I know this is a fairly simple problem but its been years since I've looked at SQL.I have two tables that contain information..

我知道这是一个相当简单的问题，但自从我研究SQL以来已经有好几年了。我有两张包含信息的表。

The table Business Partners

表业务合作伙伴

Partner
1
2
3

and the table Business Relationships

以及表业务关系

RelationshipNum Partner1 Partner2 Relationshiptype
000001          1        2        OCDL

My query should take the values for Partner1 and Partner2 and search the field Partners in Business Partners. If both values on the same entry in Business Relationships are present the entire line in Business Relationships should be returned. If only one or none are present we can ignore it.

我的查询应该获取Partner1和Partner2的值，并在业务合作伙伴中搜索字段合作伙伴。如果业务关系中相同条目上的两个值都存在，则应该返回业务关系中的整个行。如果只有一个或没有，我们可以忽略它。

This is what I have so far but its returning no values, I've tested the dataset in excel(too large to be a viable option) and there are definitely matches.

这是我目前所拥有的，但它不返回任何值，我已经在excel中测试了数据集(太大了，不能作为一个可行的选项)，而且肯定存在匹配。

SELECT  [Business Relationships].*
FROM [Business Relationships] 
INNER JOIN [Business Partners] ON ([Business Partners].Partner = [Business Relationships].Partner1) 
                               AND ([Business Partners].Partner = [Business Relationships].Partner2);

2 个解决方案

#1

Try with two INNER JOIN as below

试试下面的两个内连接。

SELECT  BR.*
FROM ([Business Relationships] BR
INNER JOIN [Business Partners] BP1 ON BP1.[Partner] = BR.[Partner1])
INNER JOIN [Business Partners] BP2 ON BP2.[Partner] = BR.[Partner2]

#2

Use double EXISTS to find rows to return:

使用double存在找到要返回的行:

SELECT  BR.*
FROM [Business Relationships] BR
WHERE EXISTS (select 1 from [Business Partners] where Partner = BR.Partner1)
  AND EXISTS (select 1 from [Business Partners] where Partner = BR.Partner2)

Will never return any duplicates! (Which JOIN solutions may do, depending on data.)

将永远不会返回任何副本!(根据数据，连接解决方案可能会这样做。)

#1