Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: recursive sql query

    Hi,

    i have the following table structure:

    id | parentID
    1 | NULL
    2 | 1
    3 | 2
    4 | 3

    im having trouble in creating a recursive query to return all the parents of a particular id. Have anyone ever done this?

    thanks in advance,

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    What goes behind this is beyond me, but... do you mean to get the ID of a parent (parentid is null) by executing a _single_ sql-statement _one_ time with a where-clause indicating the id?

  3. #3
    Join Date
    Sep 2004
    Posts
    6
    i want to have a query where i can retrieve the parent of a given id.
    for example, if i give the id 4 then the query will return a table that contains all the parent of '4' that is 3,2, and 1.

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    declare @id table(id1 int)

    INSERT INTO @id
    select parentID from tableA where id=4

    while @@rowcount<>0
    INSERT INTO @id
    select parentID from tableA as t
    inner join @id as i1 on i1.id1=t.id
    left outer join @id as i2 on i2.id1=t.parentID
    where i2.id1 is null


    select * from @id
    Last edited by theguru; 09-29-04 at 09:58.

  5. #5
    Join Date
    Sep 2004
    Posts
    6
    hmm .. it doesnt seem to work.

    i think it end up in a infinite loop.

  6. #6
    Join Date
    Mar 2004
    Posts
    80
    correction
    add one more condition to check parentId value.
    I think it will work now.

    <code>
    INSERT INTO @id
    select parentID from tableA where id=4 and t.parentId is not null

    while @@rowcount<>0
    INSERT INTO @id
    select parentID from tableA as t
    inner join @id as i1 on i1.id1=t.id
    left outer join @id as i2 on i2.id1=t.parentID
    where i2.id1 is null and t.parentId is not null


    select * from @id

    </code>

  7. #7
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    11
    It's easy, neat & efficient with SQL Server 2005 (at least it will be when Microsoft eventually release it). SQL Server 2005 includes a new object, the Common Table Expression (CTE), that can be recursive as can be seen in the code below that I whipped up in about 15 minutes this morning on the train. CTEs are practically purpose built to handle the hierarchial requirements of problems such as this (parent/child or employee/manager type relationships):
    Code:
    -- Just set up the scenario
      create table #tmp
       (
       	[id] smallint not null identity(1,1),
       	[parentid] smallint null
       );
       
       set nocount on;
       insert into #tmp ([parentid]) values (null);
       insert into #tmp ([parentid]) values (1);
       insert into #tmp ([parentid]) values (2);
       insert into #tmp ([parentid]) values (3);
       set nocount off;
       
       select * from #tmp;
       
       
       -- OK, now the good stuff...set up the CTE
       with parents (ParentID, ChildID, Level) as
       (
        	-- Anchor member
      	select parentid, [id], 0 from #tmp
       	where [id] = 4	-- Pick the child whose parent tree you want to see
       
       	union all
       
       	-- Get the next level of hierarchy recursion
       	select t.parentid, t.[id], Level + 1 from parents p
       		inner join #tmp t on t.[id] = p.ParentID
       )
       
       -- Now query it appropriately
       select distinct ParentID, ChildID, Level from parents
       where Level > 0	-- Don't return the initial anchor member, just its parents (higher levels)
       order by ChildID;
       
       
       
       drop table #tmp;
    mike hodgson
    database administrator
    mallesons stephen jaques
    http://www.mallesons.com

    'It is a far, far better thing that I do, than I have ever done; it is a far, far better rest that I go to than I have ever known.'

  8. #8
    Join Date
    Sep 2004
    Posts
    6

    Thumbs up

    thanks for the responses and the help guys! It works perfect !

    Anyone have done it the other way around (i.e given the parent then it will returns all the child of the parent). so if i give the id 1 it will return me with 2,3, and 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
  •