SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive Queries in SQL:1999 and SQL Server 2005


Recursive Queries in SQL:1999 and SQL Server 2005

Author
Message
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9629 Visits: 1407
OK Jeff, Good one. A very helpful analysis...........



RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33654 Visits: 9518
Great Article!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Paul Sinnema-443242
Paul Sinnema-443242
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
ntaylor-739763
ntaylor-739763
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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).
Paul Sinnema-443242
Paul Sinnema-443242
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
Thanks for the reply. Solved it in a stored procedure. Maybe not so nice, but it works.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206791 Visits: 41960
Two way street, here... would you mind posting your solution? Thanks. Smile

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul Sinnema-443242
Paul Sinnema-443242
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206791 Visits: 41960
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search