• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • 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

0
yo_bee
Asked:
yo_bee
1 Solution
 
penyCuicasCommented:
are you sure the field ShortDesc and mm_name are primary key or unique for each table??
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
They are unique, but not Primarys
0
 
TempDBACommented:
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
yo_beeDirector of Information TechnologyAuthor 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.

0
 
TempDBACommented:
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.
0
 
Simone BSenior E-Commerce AnalystCommented:
Do you get the same number of results when you use the same query with Select Distinct ...?
0
 
yo_beeDirector of Information TechnologyAuthor 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?
0
 
yo_beeDirector of Information TechnologyAuthor 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
0
 
vdr1620Commented:
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
WHERE MM_Name Is NOT NULL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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