当我只想要一行时，SQL JOIN 返回多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15163615/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL JOIN returning multiple rows when I only want one row
提问by Shai Cohen
I am having a slow brain day...
我的大脑一天很慢...
The tables I am joining:
我加入的表：
Policy_Office:
政策_办公室：
PolicyNumber OfficeCode
1 A
2 B
3 C
4 D
5 A
Office_Info:
办公室_信息：
OfficeCode AgentCode OfficeName
A 123 Acme
A 456 Acme
A 789 Acme
B 111 Ace
B 222 Ace
B 333 Ace
... ... ....
I want to perform a search to return all policies that are affiliated with an office name. For example, if I search for "Acme", I should get two policies: 1 & 5.
我想执行搜索以返回与办公室名称相关联的所有政策。例如，如果我搜索“Acme”，我应该得到两个策略：1 和 5。
My current query looks like this:
我当前的查询如下所示：
SELECT
*
FROM
Policy_Office P
INNER JOIN Office_Info O ON P.OfficeCode = O.OfficeCode
WHERE
O.OfficeName = 'Acme'
But this query returns multiple rows, which I know is because there are multiple matches from the second table.
但是这个查询返回多行，我知道这是因为第二个表中有多个匹配项。
How do I write the query to only return two rows?
如何编写查询以仅返回两行？
回答by John Woo
SELECT DISTINCT a.PolicyNumber
FROM Policy_Office a
INNER JOIN Office_Info b
ON a.OfficeCode = b.OfficeCode
WHERE b.officeName = 'Acme'
To further gain more knowledge about joins, kindly visit the link below:
要进一步了解有关联接的更多信息，请访问以下链接：
回答by Farzad
Simple join returns the Cartesian multiplication of the two sets and you have 2 A in the first table and 3 A in the second table and you probably get 6 results. If you want only the policy number then you should do a distinct on it.
简单连接返回两个集合的笛卡尔乘法，第一个表中有 2 个 A，第二个表中有 3 个 A，您可能会得到 6 个结果。如果你只想要保单号码，那么你应该在上面做一个不同的。
