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
Change is inevitable... Change for the better is not.
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.
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
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
August 9, 2008 at 10:54 am
Thanks Paul.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply