Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Lightbulb Unanswered: SQL Select Recursion challenge

    I am not sure if select recursion is possible and thought I would throw this challenge to the dba community. The preference would be to create a view that does the work on the backend instead of writing frontend VB code.

    Below is script that creates and populates a temp table along with the desired result-set.

    create table #myTest
    ( [id] int identity (1,1),
    [Parent] int ,
    minutes smallint
    )

    insert into #myTest (parent,minutes) values (null,1)
    insert into #myTest (parent,minutes) values (1,2)
    insert into #myTest (parent,minutes) values (1,4)
    insert into #myTest (parent,minutes) values (3,8)

    Desired resultset:

    id Parent Totalminutes
    ----------- ----------- -------
    1 NULL 15
    2 1 2
    3 1 12
    4 3 8

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    For right now...you'd need to write a function...

    BUT if you wait for Yukon..ummm SQL Server 2005...You'll be able to do it...

    Or Oracle...CONNECT BY...been around a long time...

    DB2 V8 Has it as well....

    Need help with a function, or is this purely academic?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    Hi Brett, your help writing the function would be appreciated. I am working on a project that needs this capability and prefer handling it in SQL Server where I have the resources to throw at it.

    Thanks,

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Recursion is possible within MSSQL, but my experience is that it is more efficient to create a temporary table to accumulate the desired result set and then cycle through the source data until all the parent-child relationships have been searched.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2002
    Location
    Philadelphia, PA
    Posts
    14
    You could do it in SQL with a different design. Joe Celko explains at http://tinyurl.com/2abk7

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •