Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Recursive Queries in SQL:1999 and SQL Server 2005 Expand / Collapse
Author
Message
Posted Sunday, May 18, 2008 10:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
OK Jeff, Good one. A very helpful analysis...........


Post #502625
Posted Sunday, May 18, 2008 11:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Great Article!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #502636
Posted Thursday, August 7, 2008 6:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 9, 2008 6:39 AM
Points: 3, Visits: 10
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.

WITH	MyResult(RelationContactContactID, SourceContactID, DestinationContactID, Hops)
AS
(
-- First itteration
SELECT RelationContactContactID,
SourceContactID,
DestinationContactID,
1 Hops
FROM tRelationContactContact rcc

UNION ALL

-- Second itteration
SELECT rcc.RelationContactContactID,
rcc.SourceContactID,
rcc.DestinationContactID,
Hops + 1 Hops
FROM tRelationContactContact rcc,
MyResult myr
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
FROM MyResult
WHERE SourceContactID = 543
OR DestinationContactID = 543
ORDER BY
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.
Post #548218
Posted Thursday, August 7, 2008 11:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2008 2:59 PM
Points: 41, Visits: 41
You may not think this is a tree structure but for each individual starter node, for all pratical puposes, it is and it would probably help you to think of it a a tree structure with some looping branches that need to be stopped.

There was an article here a month or 3 back about recursive selects and I think that would be a good way to go if they work (I've never used them).
Post #548514
Posted Friday, August 8, 2008 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 9, 2008 6:39 AM
Points: 3, Visits: 10
Thanks for the reply. Solved it in a stored procedure. Maybe not so nice, but it works.
Post #548936
Posted Friday, August 8, 2008 5:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:06 PM
Points: 36,786, Visits: 31,243
Two way street, here... would you mind posting your solution? Thanks. :)

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #549011
Posted Friday, August 8, 2008 6:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 9, 2008 6:39 AM
Points: 3, Visits: 10
Yep, no problem. Here it is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- This Procedure gets all the Relations of a contact
-- Parameter: ContactID: The Key of the Contact to get the relations for.
-- Parameter: Hops: How deep are we going into all of the relations.
ALTER PROCEDURE GetRelations (@ContactID int, @Hops int)
AS
BEGIN
-- First get the Anchor part of the selection into a temporary table
SELECT RelationContactContactID, SourceContactID, DestinationContactID
INTO #tmp_RelationContactContact
FROM tRelationContactContact rcc
WHERE ( rcc.SourceContactID = @ContactID
OR rcc.DestinationContactID = @ContactID
)
AND rcc.DeletedOn IS NULL

WHILE (@Hops > 1)
BEGIN
SET @Hops = @Hops - 1

-- Tell T-SQL that we would like to insert just using the identities of the fields.
SET IDENTITY_INSERT #tmp_RelationContactContact ON

-- Here we get the Relations one step deeper and also insert them into the temp table
INSERT
INTO #tmp_RelationContactContact
( RelationContactContactID, SourceContactID, DestinationContactID )
SELECT trc1.RelationContactContactID, trc1.SourceContactID, trc1.DestinationContactID
FROM
( -- This subselect will only contain those relations that are not in the temp table
SELECT RelationContactContactID, SourceContactID, DestinationContactID
FROM tRelationContactContact
WHERE DeletedOn IS NULL

EXCEPT

SELECT RelationContactContactID, SourceContactID, DestinationContactID
FROM #tmp_RelationContactContact
) trc1,
#tmp_RelationContactContact trc2
WHERE ( trc1.SourceContactID = trc2.SourceContactID
OR trc1.DestinationContactID = trc2.DestinationContactID
OR trc1.SourceContactID = trc2.DestinationContactID
OR trc1.DestinationContactID = trc2.SourceContactID
)

-- If nothing was inserted we can end the loop here.
IF (@@ROWCOUNT = 0)
BEGIN
SET @Hops = 0
END
END

-- Return all the rows that were inserted into the temp table
SELECT rcc.*
FROM #tmp_RelationContactContact trcc,
tRelationContactContact rcc
WHERE trcc.RelationContactContactID = rcc.RelationContactContactID

-- Delete the temp table
DROP TABLE #tmp_RelationContactContact
END

Post #549059
Posted Saturday, August 9, 2008 10:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:06 PM
Points: 36,786, Visits: 31,243
Thanks Paul.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #549786
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse