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 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 @ 6:47 AM
Points: 38,073, Visits: 34,982
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 @ 6:47 AM
Points: 38,073, Visits: 34,982
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