August 7, 2008 at 11:17 am
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).
August 8, 2008 at 2:36 am
Thanks for the reply. Solved it in a stored procedure. Maybe not so nice, but it works.
August 8, 2008 at 5:13 am
Two way street, here... would you mind posting your solution? Thanks. 🙂
--Jeff Moden
August 8, 2008 at 6:28 am
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
August 9, 2008 at 10:54 am
Thanks Paul.
--Jeff Moden
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply