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
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[tRelationContactContact](
[RelationContactContactID] [int] IDENTITY(1,1) NOT NULL,
[SourceContactID] [int] NULL,
[DestinationContactID] [int] NULL
CONSTRAINT [PK_PK_PersonInstitutionRelations] PRIMARY KEY CLUSTERED
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.
WITH MyResult(RelationContactContactID, SourceContactID, DestinationContactID, Hops)
-- First itteration
FROM tRelationContactContact rcc
-- Second itteration
Hops + 1 Hops
FROM tRelationContactContact rcc,
WHERE ( myr.SourceContactID = rcc.SourceContactID
OR myr.DestinationContactID = rcc.DestinationContactID
OR myr.SourceContactID = rcc.DestinationContactID
OR myr.DestinationContactID = rcc.SourceContactID
AND myr.RelationContactContactID <> rcc.RelationContactContactID
AND Hops < 2
SELECT DISTINCT RelationContactContactID, SourceContactID, DestinationContactID
WHERE SourceContactID = 543
OR DestinationContactID = 543
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?