Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Question Unanswered: Recursive SELECT...HOWTO

    My turn:

    I have 2 tables:

    t1 (f1 int PK)
    t2 (f1 int, L1 int)

    t2.f1 is an FK to t1.f1

    t2.L1 may contain the value that exists in t1.f1.

    The task is to retrieve the entire chain.

    t1:

    f1
    ---
    1
    2
    3
    4
    5

    t2:

    f1 L1
    --- ---
    1 2
    2 3
    3 4
    4 5

    The result should have:

    Parent Child Level
    ------- ------ -------
    1 2 1
    2 3 2
    3 4 3
    etc...

    Of course the actual structure is differernt, but the concept is the same. The customer wants to have the flexibility to either supply t1.f1 value, or retrieve parent/child/level info for ALL.

    Any idea on how to accomplish it in one SELECT????

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Recursive SELECT...HOWTO

    I don't have the answer, but I recognize your problem. Actually, you don't need table t1, but it's the recursive structure of t2, which forms the problem. I know that this is a standard problem in DWH projects, and the standard answer is to transform the recursive structure in a more flat form, including the level. I've never seen doing a query this task, but (of course) recursive procedures.

    So, I would search in making a recursive stored procedure returning your recordset.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    I suspect the only way to do this in a single select is to use a function. Any type of recursion would hit the 32 nest level limit.

    You would implement a loop in the functionreturniong a table probably.

    This is an SP to do a similar thing with a loop

    http://www.nigelrivett.net/RetrieveTreeHierarchy.html

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A function called by a select statement is the way to go. I use a recursive method similar to nigelrivett's, which I detailed here:

    http://dbforums.com/t901390.html

    ...the difference being that nigelrivetts returns circular references, while mine (using the exists criteria) filters out circular references making the ID field unique in the result set. Which to use depends on the output you want.

    blindman

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Unfortunately I don't know how to write functions...in 7.0

    I'll try OPENROWSET, since I love it so much

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    After long hours of negotiations and a full functional test of major processes the business agreed to convert the app to 2K. NO MORE OPENROWSET!!! I wrote it in 2 functions.

    SET ANSI_PADDING ON
    GO

    if exists (select * from sysobjects where id = object_id('dbo.tblLink_ProfileItems') and sysstat & 0xf = 3)
    drop table dbo.tblLink_ProfileItems
    GO

    CREATE TABLE dbo.tblLink_ProfileItems (
    ProfileID int NOT NULL ,
    ProfileItemNumber int IDENTITY (1, 1) NOT NULL ,
    ItemTypeId int NOT NULL ,
    ItemLinkID int NOT NULL ,
    CONSTRAINT FK_tblLink_ProfileItems_tblMainProfiles
    FOREIGN KEY
    (
    ProfileID
    ) REFERENCES dbo.tblMainProfiles (
    ProfileID
    )
    )
    GO

    SET ANSI_PADDING ON
    GO

    if exists (select * from sysobjects where id = object_id('dbo.tblMainProfiles') and sysstat & 0xf = 3)
    drop table dbo.tblMainProfiles
    GO

    CREATE TABLE dbo.tblMainProfiles (
    ProfileID int IDENTITY (1, 1) NOT NULL ,
    ProfileName varchar (50) NOT NULL ,
    ProfileVersion varchar (10) NULL ,
    ProfileShortName varchar (50) NOT NULL ,
    ProfileStatus int NOT NULL ,
    ProfileType int NOT NULL ,
    ProfileComplexityID int NOT NULL ,
    ProfileLineageID int NOT NULL ,
    PotentialBCProfile bit NOT NULL ,
    CONSTRAINT PK_dbo_tblMainProfiles
    PRIMARY KEY NONCLUSTERED (ProfileID)
    )
    GO

    create function dbo.fn_Profile_Parents (
    @ProfileID int = null)
    returns @tbl table (
    RecID int identity(1,1) not null ,
    ParentID int not null ,
    ChildID int null,
    Seq int not null)
    as begin
    insert @tbl (ParentID, ChildID, Seq)
    select top 100 percent
    Parent = ProfileID, Child = ProfileID, 0
    from tblMainProfiles m (nolock)
    where exists (
    select * from tblLink_ProfileItems i (nolock)
    where m.ProfileID = i.ProfileID)
    and ProfileID = @ProfileID
    return
    end
    go

    alter function dbo.fn_Profile_Children (
    @ParentID int = null)
    returns @tbl table (
    RecID int identity(1000000,1) not null ,
    ParentID int not null,
    ChildID int null,
    Seq int not null )
    as begin
    declare @i int
    set @i = 1
    insert @tbl (ParentID, ChildID, Seq)
    select @ParentID, ItemLinkID, @i
    from tblLink_ProfileItems i (nolock)
    where i.ProfileID = @ParentID and ItemTypeID = 2
    while @@rowcount > 0 begin
    set @i = @i + 1
    insert @tbl (ParentID, ChildID, Seq)
    select t.ChildID, ItemLinkID, @i from @tbl t
    left outer join tblLink_ProfileItems i (nolock)
    on t.ChildID = i.ProfileID and ItemTypeID = 2
    left outer join @tbl t1
    on ( t.ChildID = t1.ParentID)
    where t1.ParentID is null
    and t.ChildID is not null
    end
    return
    end
    go



    The call that returns the resultset is as follows:

    select ParentID, ChildID, Seq from fn_Profile_Parents(1435)
    union
    select ParentID, ChildID, Seq from fn_Profile_Children(1435)
    order by 3, 2 asc


    ...and the result is:


    ParentID ChildID Seq
    ----------- ----------- -----------
    1435 1435 0
    1435 1344 1
    1435 1383 1
    1383 1210 2
    1383 1384 2
    1344 1396 2
    1344 1507 2
    1384 NULL 3
    1507 NULL 3
    1396 1445 3
    1210 1526 3
    1445 NULL 4
    1526 NULL 4


Posting Permissions

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