Hi all,

I've got a problem to solve and I don't seem to be able to get the grip on it I need. The problem is as follows. We have a table called RelationContactContact like so:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tRelationContactContact](

[RelationContactContactID] [int] IDENTITY(1,1) NOT NULL,

[SourceContactID] [int] NULL,

[DestinationContactID] [int] NULL

CONSTRAINT [PK_PK_PersonInstitutionRelations] PRIMARY KEY CLUSTERED

(

[RelationContactContactID] ASC

)

I've removed all the Fields that aren't interesting for the problem.

What it represents is a self-referencing table via the Fields SourceContactID and DestinationContactID. In fact any Contact (in a separate Contact Table) can have a relation to any other Contact. In fact this is not a Tree structure but a Cloud of relations. Circular Connections are possible (A has a relation to B, B has a relation to C, C has a relation to A). This is also where the problem begins.

I've tried to write a Query that resolves all the Connections 1 Contact can have (i.e. A -> B -> C). Here's what I've tried so far. It works but it is way to slow, when the test on the Hops allows values over 2, and has a cartesian product which in the end is filtered out by the SELECT DISTINCT.

WITHMyResult(RelationContactContactID, SourceContactID, DestinationContactID, Hops)

AS

(

-- First itteration

SELECTRelationContactContactID,

SourceContactID,

DestinationContactID,

1 Hops

FROMtRelationContactContact rcc

UNIONALL

-- Second itteration

SELECTrcc.RelationContactContactID,

rcc.SourceContactID,

rcc.DestinationContactID,

Hops + 1 Hops

FROMtRelationContactContact rcc,

MyResultmyr

WHERE(myr.SourceContactID= rcc.SourceContactID

ORmyr.DestinationContactID= rcc.DestinationContactID

ORmyr.SourceContactID= rcc.DestinationContactID

ORmyr.DestinationContactID= rcc.SourceContactID

)

ANDmyr.RelationContactContactID<> rcc.RelationContactContactID

ANDHops < 2

)

SELECTDISTINCT RelationContactContactID, SourceContactID, DestinationContactID

FROMMyResult

WHERESourceContactID= 543

ORDestinationContactID= 543

ORDERBY

RelationContactContactID, SourceContactID, DestinationContactID

What I need to do is of course filter out the result of the first (anchor) query in the Select of the second (recursive) query. But I also need to match the field Destination- and SourceContactID with the previous result. T-SQL doen't allow CTE's to use the intermediate result twice. So I'm a bit stuck here. Any ideas?

Cheers,

Paul.