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:

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 

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

Adding a where clause isn't likely to get you the result you're looking for.

I'm a bit confused by the situation you described restricting your ability to mess with the joins, but a group by is the only viable option I can think of.

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

You can use Select distinct But you will have to add [address].name column to select list like this (Alternatively you can remove [address].name column from your orderby clause.:

SELECT DISTINCT
[address].name as [Address],
    [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

it should work, but you probably want to rewrite your query. In order to do this, can you please provide table mappings for your database, so we can help?

Add DISTINCT after your SELECT

SELECT DISTINCT
    [addresstype].name As [Type], 
    [address].city As [City], address.statecode As [State], 
    [address].postalcode As [Zip], 
    [address].addressid As [Id],
    [address].name
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  

added the [address].name to the SELECT list to get past the ORDER BY error you were getting

Understanding why you're getting multiple repeating rows from a query is a critical skill to learn with SQL - and one of the few places where I'd recommend using SELECT * rather than using a column list.

Once you're viewing the entire (wide) result set, you can hopefully identify where the entire result set contains differences (even though the projected result set containing only 5 columns appears identical). It's only by examining those differences that you can determine how to update your original query:- either by adding conditions to the WHERE clause, adding conditions to one of the ON clauses of a JOIN, or introducing a new JOIN that can reduce the result set.