CODEWITHSUNDEEP
sqljoin
Shai Cohen
Shai Cohen

Reputation: 6249

SQL JOIN returning multiple rows when I only want one row

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?

Upvotes: 5

Views: 57603

Answers (3)

Henrik Carlsen
Henrik Carlsen

Reputation: 65

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

Upvotes: 1

Farzad
Farzad

Reputation: 1142

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.

Upvotes: 6

John Woo
John Woo

Reputation: 263793

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:

Upvotes: 14

Related Questions