• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Inner Join Statement returns duplicates

I am trying to compare two tables using inner join.
One table is in one DB and the other is in a different DB on a different server.
I used Link Server under Server Objects and Created a query with an inner join.  

If I do each one by itself I return a couple of hundred rows, but if I do the inner join I get thousands of rows with duplicate rows.

Why is this.

SELECT   Matters_1.MatterId, Matters_1.ClientSort, Matters_1.ShortDesc, Matters_1.ClientID, Matters_1.MatterNum,Matters_1.Description, dbo.matter.mm_name AS LKDESC
FROM         [FLHPROLAW\PROLAW].prolaw.dbo.Matters AS Matters_1 INNER JOIN
                      dbo.matter ON Matters_1.ShortDesc = dbo.matter.mm_name
WHERE     (Matters_1.MatterID LIKE '110001%')

Open in new window

1 Solution
are you sure the field ShortDesc and mm_name are primary key or unique for each table??
yo_beeDirector of ITAuthor Commented:
They are unique, but not Primarys
I will recommend verify the field properties again. Or else you can check for the duplicates in the respective table itself it there is any.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

yo_beeDirector of ITAuthor Commented:
What if I did:

Select *
From Matter_ID
Where Matter_ID = [FLHPROLAW\PROLAW].prolaw.dbo.Matters.ShortDesc

But I get a return error:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "FLHPROLAW\PROLAW.prolaw.dbo.Matters.ShortDesc" could not be bound.

The statement is wrong. What you are trying to do is
select *
from A_tbl
where A_tbl = B_tbl

We can compare two tables this way. We can rather join them, compare their fields.
Simone BCommented:
Do you get the same number of results when you use the same query with Select Distinct ...?
yo_beeDirector of ITAuthor Commented:
The DISTINCT kicks back an error
Msg 421, Level 16, State 1, Line 4
The text data type cannot be selected as DISTINCT because it is not comparable.

I found that there is a NULL value for one MM_NAME.
How would I exlude that from the Join?
yo_beeDirector of ITAuthor Commented:
All of our Client-Matters has a General and Misc.
So when I challenge that value it was return it as a false record.

Thanks for you help
So, Inorder to use the Text Field ..just convert it into Convert(varchar(Length),FieldName) as FieldAlias

-- You can exclude the NULL values using a Where Clause

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now