SQL returning repeated results

Question

Why would the following SQL query return repeated results? I only want 3 rows to return in my result set. I'm guessing my joins are incorrect. The constraints should be explanatory from the query joins. If you need additional info, please ask.

SELECT 
    [addresstype].name As [Type], 
    [address].city As [City], address.statecode As [State], 
    [address].postalcode As [Zip], 
    [address].addressid As [Id] 
FROM  
    [address]
    LEFT OUTER JOIN [contact_address] ON [address].addressid = [contact_address].addressid 
    LEFT OUTER JOIN [addresstype] ON [addresstype].addresstypeid = [contact_address].addresstypeid 
    LEFT OUTER JOIN [clientcontact] ON dbo.contact_address.contactid = [clientcontact].contactid 
WHERE  
    [contact_address].contactid = 12538 
ORDER BY 
    [address].name, [address].statecode, [address].city  

Results:

======================

More Info

It looks like I have multiple clients. The reason I have this join is not for this query, but for another query that relies on this query. It's being built in a custom made rules engine in .NET code. Another query needs this clientcontact join because there is a temp table being built from a UNION query. I wouldn't really need this table (clientcontact) with that join if that was the case. I get multiple rows because I have multiple clientids in the clientcontact table. In other words, this contact works at all of these clients. However, I'd like to put in a WHERE clause so I get 3 rows, but I can't mess with the JOINS. Those are shared, from my explanation above. How can this be done? ...pardon my RIGHT JOIN .. shouldn't change anything. Don't let that confuse you. :-)

New Query to show this:

SELECT 
    dbo.clientcontact.clientcontactid ,
    dbo.clientcontact.clientid ,
    dbo.clientcontact.contactid
    --[addresstype].name As [Type], 
    --[address].city As [City], address.statecode As [State], 
    --[address].postalcode As [Zip], 
    --[address].addressid As [Id] 
FROM  
    [address]
    LEFT OUTER JOIN [contact_address] ON [address].addressid = [contact_address].addressid 
    LEFT OUTER JOIN [addresstype] ON [addresstype].addresstypeid = [contact_address].addresstypeid 
    right JOIN [clientcontact] ON dbo.contact_address.contactid = [clientcontact].contactid 
WHERE  
    [contact_address].contactid = 12538 
ORDER BY 
    [address].name, [address].statecode, [address].city  

=================

More Updates

A few were confused as to why I couldn't remove the clientcontact join. It's because another query in our .NET rules engine is using this same query. See the second query of the UNION query below. If there's absolutely no way to get 3 rows from this by keeping that join, then that is the answer I guess. Then I need to separate the two.

SELECT 
    client_addressexternal.address_table_type As [Address Record Type], 
    addresstype.name As [Type], 
    CASE WHEN client_addressexternal.address_table_type = 'CLIENT Address' THEN '<a href="/ClientServices/ManageClients/ClientDetails/ClientAddresses.aspx?Id=' + CONVERT(VARCHAR,client_addressexternal.addressid) + '&ClientId=' + CONVERT(VARCHAR,client_addressexternal.client_id) + '&SourceClientId=14103">' + address.name + '</a>' + '<br /><b>Client Name:</b> ' + client_addressexternal.client_full_name ELSE client_addressexternal.contact_full_name END As [Address Name], 
    dbo.limssubstring(dbo.LIMSNullString(address1) + '<br />' + dbo.LIMSNullString(address2), 84) As [Address], 
    address.city As [City], address.statecode As [State], 
    address.postalcode As [Zip], 
    CASE client.clientid WHEN 14103 THEN '' ELSE client.name END As [From Parent Client], 
    address.addressid As [Id] 
FROM  
address 
    JOIN (

        SELECT client_address.clientid, client_address.addressid, client_address.addresstypeid, depth, 'CLIENT Address' AS 'address_table_type', '' as 'contact_full_name', client.name as 'client_full_name', client_address.clientid as 'client_id', '' as 'contact_id'
        FROM dbo.fnClientRelatives(14103, 0, 1, 0) relatives
        inner join client_address on client_address.clientid = relatives.clientid
        LEFT OUTER JOIN client ON relatives.clientid = dbo.client.clientid

        UNION

        SELECT clientcontact.clientid, contact_address.addressid, contact_address.addresstypeid, 999 [depth], 'CONTACT Address' AS 'address_table_type', address.name + '<br /><b>Contact Name:</b> ' + LTRIM(RTRIM(ISNULL(contact.firstname, '') + ISNULL(' ' + contact.middleinitial + ' ', ' ') + ISNULL(contact.lastname, ''))), '' as 'client_full_name', clientcontact.clientid as 'client_id', clientcontact.contactid as 'contact_id'
        from clientcontact 
        inner join contact_address ON contact_address.contactid=clientcontact.contactid and clientcontact.clientid=14103
        LEFT OUTER JOIN [contact] ON [clientcontact].contactid = [contact].contactid
        LEFT OUTER JOIN [address] ON contact_address.addressid = address.addressid

    ) AS client_addressexternal ON client_addressexternal.addressid = address.addressid 
    JOIN client ON client.clientid = client_addressexternal.clientid 
    JOIN addresstype on addresstype.addresstypeid = client_addressexternal.addresstypeid 
 ORDER BY 
    depth,address.statecode, address.city, address.name     

Here's the function if you're super interested:

GO
/****** Object:  UserDefinedFunction [dbo].[fnClientRelatives]    Script Date: 07/29/2011 12:48:24 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
--your basic recursive tree searcher.
--childrennotparents = 1 means you'll get children. = 0 means you'll get parents
--@recursive = 1 means it finds all children, grandchildren, etc... or whatever
-- The depth is the base level to start incrementing each level, if set to zero, the @clientid will also be part of the results
ALTER  FUNCTION [dbo].[fnClientRelatives]
(
    @clientId INT,
    @childrenNotParents BIT,
    @recursive bit,
    @depth int
)
RETURNS @clientids TABLE (clientid INT primary key clustered, depth int)
AS
begin

-- Add the parent client id if the depth is zero
if @depth = 0
begin
    INSERT INTO @clientids VALUES (@clientid, @depth)
end
set @depth = @depth + 1

IF @childrenNotParents = 1  
begin
    DECLARE clientids CURSOR FOR  
        SELECT clientid
        FROM client
        where parentclientid = @clientId
END--/if childrennotparents
ELSE--if not childrennotparents  
BEGIN  
    DECLARE clientids CURSOR FOR  
        SELECT parentclientid
        FROM client
        where clientid = @clientid
END--/if not childrennotparents

OPEN clientids
DECLARE @nextClientID INT
FETCH clientids INTO @nextClientID
--@nextClientID may be null if we're loading parents, and the
--current client has null for a parent id.
WHILE @@FETCH_STATUS = 0 AND @nextClientID IS NOT NULL
BEGIN  
    INSERT INTO @clientids
    VALUES (@nextclientid, @depth)

    IF @recursive = 1  
    BEGIN  
        INSERT INTO @clientids  
            SELECT * FROM dbo.fnClientRelatives(@nextclientid, @childrenNotParents, @recursive, @depth)    
    END--IF @recursive = 1    
FETCH clientids INTO @nextclientid  
END--WHILE @@FETCH_STATUS = 0  

CLOSE clientids  
DEALLOCATE clientids  

RETURN   
END--/IssueRelatives

Database Diagram for Addresses:

Answer 1

More information would be helpful, but based on what you've provided, I would say that you have multiple records in the clientcontact table.

Add the DISTINCT keyword to your select statement or remove the unnecessary join (you're not using anything from the clientcontact table).

NOTE: A lot of people use the DISTINCT keyword to cover up a poorly written query. While DISTINCT will give you the results that you're expecting, it doesn't really fix your problem - it covers it up. Make sure you understand why your getting duplicate records before you think about using DISTINCT.

EDIT:

If you can't remove the join (still not sure I understand why), and DISTINCT doesn't work (still not sure I understand why), then add a GROUP BY

GROUP BY [addresstype].name,
         [address].city,
         [adress].statecode,
         [address].postalcode,
         [address].addressid 

Answer 2

You need a SELECT DISTINCT, because relational databases are based on multisets (although the relational data-model is based on sets).