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

LVL 27
yo_beeDirector of Information TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

penyCuicasCommented:
are you sure the field ShortDesc and mm_name are primary key or unique for each table??

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yo_beeDirector of Information TechnologyAuthor Commented:
They are unique, but not Primarys
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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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.

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.
Simone BSenior E-Commerce AnalystCommented:
Do you get the same number of results when you use the same query with Select Distinct ...?
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?
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.