Inner Join Statement returns duplicates

Posted on 2011-10-04
Last Modified: 2013-11-05
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

Question by:yo_bee
    LVL 2

    Accepted Solution

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

    Author Comment

    They are unique, but not Primarys
    LVL 25

    Expert Comment

    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.
    LVL 21

    Author Comment

    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.

    LVL 25

    Expert Comment

    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.
    LVL 11

    Expert Comment

    by:Simone B
    Do you get the same number of results when you use the same query with Select Distinct ...?
    LVL 21

    Author Comment

    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?
    LVL 21

    Author Closing Comment

    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
    LVL 16

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now