Recursive Queries in SQL:1999 and SQL Server 2005

Question

Recursive Queries in SQL:1999 and SQL Server 2005

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

ntaylor-739763 SSC Veteran Points: 233 More actions · Answer 1

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 SSC Rookie Points: 25 More actions · Answer 2

Thanks for the reply. Solved it in a stored procedure. Maybe not so nice, but it works.

Jeff Moden SSC Guru Points: 1004589 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul Sinnema-443242 SSC Rookie Points: 25 More actions · Answer 4

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.

ALTERPROCEDUREGetRelations (@ContactID int, @Hops int)

AS

BEGIN

-- First get the Anchor part of the selection into a temporary table

SELECTRelationContactContactID, SourceContactID, DestinationContactID

INTO#tmp_RelationContactContact

FROMtRelationContactContactrcc

WHERE(rcc.SourceContactID= @ContactID

ORrcc.DestinationContactID= @ContactID

)

ANDrcc.DeletedOnIS 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.

SETIDENTITY_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)

SELECTtrc1.RelationContactContactID, trc1.SourceContactID, trc1.DestinationContactID

FROM

(-- This subselect will only contain those relations that are not in the temp table

SELECTRelationContactContactID, SourceContactID, DestinationContactID

FROMtRelationContactContact

WHEREDeletedOnIS NULL

EXCEPT

SELECTRelationContactContactID, SourceContactID, DestinationContactID

FROM#tmp_RelationContactContact

)trc1,

#tmp_RelationContactContacttrc2

WHERE(trc1.SourceContactID= trc2.SourceContactID

ORtrc1.DestinationContactID= trc2.DestinationContactID

ORtrc1.SourceContactID= trc2.DestinationContactID

ORtrc1.DestinationContactID= trc2.SourceContactID

)

-- If nothing was inserted we can end the loop here.

IF (@@ROWCOUNT = 0)

BEGIN

SET @Hops = 0

END

-- Return all the rows that were inserted into the temp table

SELECTrcc.*

FROM#tmp_RelationContactContacttrcc,

tRelationContactContactrcc

WHEREtrcc.RelationContactContactID = rcc.RelationContactContactID

-- Delete the temp table

DROPTABLE#tmp_RelationContactContact

END

Jeff Moden SSC Guru Points: 1004589 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Recursive Queries in SQL:1999 and SQL Server 2005

Cookies on SQLServerCentral