为什么下面的SQL查询会返回重复的结果？我只想要3行结果。我猜测我的连接有误。限制应该可以从查询连接中说明。如果需要额外信息，请询问。
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
结果：
======================
更多信息
看起来我有多个客户端。 我使用这个连接不是为了这个查询，而是依赖于这个查询的另一个查询。 这是在.NET代码中构建的自定义规则引擎。 另一个查询需要这个clientcontact连接，因为从UNION查询构建了一个临时表。如果那样的话，我就不需要这个表（clientcontact）来加入该连接。 我会获得多行，因为在clientcontact表中有多个客户端ID。 换句话说，这个联系人在所有这些客户端工作。 但是，我想放入一个WHERE子句，以便获取3行，但我不能修改JOINS。 以上是共享的。 如何做到这一点？……请原谅我的RIGHT JOIN..这不应该改变任何东西。 不要让它困扰您。 :-)
新查询如下：
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
=================
更多更新
有些人对于我为什么不能删除clientcontact join感到困惑。这是因为我们的.NET规则引擎中的另一个查询正在使用相同的查询。请参见下面UNION查询的第二个查询。如果通过保留该连接无法从中获取3行，那么我猜那就是答案。然后我需要将它们分开。
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
如果你非常感兴趣，这里是函数：
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
地址数据库图示：
