Click here to monitor SSC
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
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

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



RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
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
         Wink
   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
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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).
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44977 Visits: 39869
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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
         Wink
   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   Wink
      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
      Wink   trc1,
         #tmp_RelationContactContact   trc2
      WHERE   (   trc1.SourceContactID      = trc2.SourceContactID
            OR   trc1.DestinationContactID   = trc2.DestinationContactID
            OR   trc1.SourceContactID      = trc2.DestinationContactID
            OR   trc1.DestinationContactID   = trc2.SourceContactID
            Wink

      -- 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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44977 Visits: 39869
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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