当我只想要一行时，SQL JOIN返回多行 [英] SQL JOIN returning multiple rows when I only want one row

问题描述

我的大脑活动缓慢...

I am having a slow brain day...

我要加入的表格:

政策办公室:

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

我想执行搜索以返回与办公室名称相关的所有策略.例如，如果我搜索"Acme"，则应该获得两个策略:1& 5.

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.

我当前的查询如下:

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?

推荐答案

SELECT  DISTINCT a.PolicyNumber
FROM    Policy_Office a
        INNER JOIN Office_Info b
            ON a.OfficeCode = b.OfficeCode
WHERE   b.officeName = 'Acme'

• SQLFiddle演示
• SQLFiddle Demo

要进一步获得有关联接的知识，请访问下面的链接:

To further gain more knowledge about joins, kindly visit the link below:

• SQL联接的可视表示

这篇关于当我只想要一行时，SQL JOIN返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！