SQL JOIN returning multiple rows when I only want one row

Question

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.

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?

Hungary

What database are you using? You have a one-to-many relationship so what is the desired result of the query? – Dorothy

You have duplicates in one of your tables. – Conall

Answer 1

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

• SQLFiddle Demo

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

• Visual Representation of SQL Joins

Haupt

That Venn diagram link abuses Venn diagrams (tables are bags of rows, not sets, among many other problems). Venn diagrams are not appropriate for explaining joins; they are appropriate to show certain things about limited special cases & parts of certain joins. That link & all the other pseudo-Venn diagrams are unclear, unexplained & misleading. Venn Diagram for Natural Join – Gaius

Answer 2

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.

Dubitation

Answer 3

(using MS-Sqlserver)

I know this thread is 10 years old, but I don't like distinct (in my head it means that the engine gathers all possible data, computes every selected row in each record into a hash and adds it to a tree ordered by that hash; I may be wrong, but it seems inefficient).

Instead, I use CTE and the function row_number(). The solution may very well be a much slower approach, but it's pretty, easy to maintain and I like it:

Given is a person and a telephone table tied together with a foreign key (in the telephone table). This construct means that a person can have more numbers, but I only want the first, so that each person only appears one time in the result set (I ought to be able concatenate multiple telephone numbers into one string (pivot, I think), but that's another issue).

; -- don't forget this one!
with telephonenumbers
as
(
  select [id]
       , [person_id]
       , [number]
       , row_number() over (partition by [person_id] order by [activestart] desc) as rowno
  from [dbo].[telephone]
  where ([activeuntil] is null or [activeuntil] > getdate()
)
select p.[id]
      ,p.[name]
      ,t.[number]
from [dbo].[person] p
left join telephonenumbers t on     t.person_id = p.id
                                and t.rowno = 1

This does the trick (in fact the last line does), and the syntax is readable and easy to expand. The example is simple but when creating large scripts that joins tables left and right (literally), it is difficult to avoid that the result contains unwanted duplets - and difficult to identify which tables creates them. CTE works great for me.

Ether