none
select Parent/Child id recursive query

    Question

  • Table

    Rank chid parid

    7       6

    5       4

    4      3

    3      2

    2      Null

     

    My output should be

     

    Rank chiid parid

    1        5       4

    2        4      3

    3        3      2

    4        2      Null

     

    parid : 6 not matching with child id, it should not come in select query

    parid : 2 has child id , so it should be appear in the select query

    Rank as row_number()

    basically , last parent id should be next row of child id, O

    only matching parid should be next row as child table..continues.....

    I need some type of sub query , I dont want link please.

    Thanks

     

    Rank ChildID ParentID
    1 5204 5201
    2 5201 5185
    3 5185 5028
    4 5028 4654
    5 4654 4597
    6 4597 4522
    7 4522 4077
    8 4077 4076
    9 4076 3920
    10 3920 3569
    11 3569 3340
    12 3340 1241
    13 1241 1171
    14 1171 391
    15 391 357
    16 357 26
    17 26 NULL

    Sample output :  parentid : 5201 in the rank2, In rank2 parentid in the third row

     

    Friday, June 13, 2008 4:44 PM

Answers

  • You might be able to use something like this as a starting point:

     

    Code Snippet

    declare @table table(child int, parid int)
    insert into @table
    select 7, 6 union all
    select 5, 4 union all
    select 4, 3 union all
    select 3, 2 union all
    select 2, null

     

    ;with list
    ( tLevel,
      child,
      parId
    ) as
    ( select
        1 as tLevel,
        child,
        parId
      from @table a
      where parId is null
      union all
      select
        tLevel + 1,
        b.child,
        b.parId
      from list a
      join @table b
        on b.parId = a.child
    )
    select
      rank() over(order by tLevel desc)
        as Rank,
      child,
      parId
    from list

     

    /* -------- Sample Output: --------
    Rank                 child       parId
    -------------------- ----------- -----------
    1                    5           4
    2                    4           3
    3                    3           2
    4                    2           NULL
    */

     

     

    Friday, June 13, 2008 5:19 PM
    Moderator
  • This works for SQL 2000:

     

    Code Snippet

    declare @table table(child int, parid int)

    insert into @table

    select 7, 6 union all

    select 5, 4 union all

    select 4, 3 union all

    select 3, 2 union all

    select 2, null

     

    declare @results table

    (rankid int identity primary key,

    child int,

    parent int)

     

    INSERT into @Results

    SELECT t1.* FROM @Table t1

    join @TABLE t2 on isnull(t1.parid, t1.child) = t2.child

    order by child desc, parid desc

     

    select * from @results

     

     

    Did I make it to simple?
    Friday, June 13, 2008 7:50 PM
    Moderator

All replies

  • Hang on and I'll get an example. Your test data shows no branching in the tree.  If that is accurate fine, but branching will add complexity and it will also require that you describe how nodes get ordered when branching is involved.  Also, it would be better if you said which version of SQL server you were using.

     

    Friday, June 13, 2008 5:03 PM
    Moderator
  •  

    SQL server 2005
    Friday, June 13, 2008 5:07 PM
  • You might be able to use something like this as a starting point:

     

    Code Snippet

    declare @table table(child int, parid int)
    insert into @table
    select 7, 6 union all
    select 5, 4 union all
    select 4, 3 union all
    select 3, 2 union all
    select 2, null

     

    ;with list
    ( tLevel,
      child,
      parId
    ) as
    ( select
        1 as tLevel,
        child,
        parId
      from @table a
      where parId is null
      union all
      select
        tLevel + 1,
        b.child,
        b.parId
      from list a
      join @table b
        on b.parId = a.child
    )
    select
      rank() over(order by tLevel desc)
        as Rank,
      child,
      parId
    from list

     

    /* -------- Sample Output: --------
    Rank                 child       parId
    -------------------- ----------- -----------
    1                    5           4
    2                    4           3
    3                    3           2
    4                    2           NULL
    */

     

     

    Friday, June 13, 2008 5:19 PM
    Moderator
  • Thanks for your inputs..

     

    Is there any other option other than CTE, because i am expecting as a Sub Query to get this output. because i need to execute in different version also .like Sql server 2000 / 7.0
    Friday, June 13, 2008 6:28 PM
  • sdfsdfsdfsdfsa

     

    Edit:

     

     Dhanapal Selvaraj wrote:

     

    SQL server 2005

     

    I think I will leave this for someone else.

    Friday, June 13, 2008 6:30 PM
    Moderator
  • Yes.. fine i agree that.

     

        Is there any alternative to write using Sub query ?

    If too many parent id is null, then i'm getting error like that

     

    Msg 530, Level 16, State 1, Line 4

    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    Friday, June 13, 2008 6:36 PM
  • Dhanapal:

     

    Please forgive my previous response; I have recycled -- better now.  Yes, if you are getting the recursion level exceeded the CTE approach will likely no perform.  Also, since SQL Server 7 is in this equation there are a number of additional limitations that I really did not anticipate. 

     

    Friday, June 13, 2008 6:57 PM
    Moderator
  • Is there any option in Sql Server 2000 ?

     

    Friday, June 13, 2008 7:09 PM
  • This works for SQL 2000:

     

    Code Snippet

    declare @table table(child int, parid int)

    insert into @table

    select 7, 6 union all

    select 5, 4 union all

    select 4, 3 union all

    select 3, 2 union all

    select 2, null

     

    declare @results table

    (rankid int identity primary key,

    child int,

    parent int)

     

    INSERT into @Results

    SELECT t1.* FROM @Table t1

    join @TABLE t2 on isnull(t1.parid, t1.child) = t2.child

    order by child desc, parid desc

     

    select * from @results

     

     

    Did I make it to simple?
    Friday, June 13, 2008 7:50 PM
    Moderator
  • Here is a recursive SQL function to return the parent/child relationships that you can tailor for your table:

     

    CREATE FUNCTION [dbo].[fGetParents] ( @startId INT )
    RETURNS @results TABLE (
        [Id] [int],
        [parentId] [int],
        [text] [varchar](128)
    )
    AS
    BEGIN
        DECLARE @Id INT
        DECLARE @parentId INT
        DECLARE @text varchar(128)

        BEGIN
            DECLARE my_cursor CURSOR LOCAL FOR
                SELECT Id, parentId, text FROM relationships
                WHERE  parentId = @startId
            OPEN my_cursor;
            FETCH NEXT FROM my_cursor INTO @Id, @parentId, @text
                WHILE @@FETCH_STATUS = 0 BEGIN
                    INSERT INTO @results VALUES (@Id, @parentId, @text)
                    INSERT INTO @results SELECT * FROM fGetParents(@Id)
                    FETCH NEXT FROM my_cursor INTO @Id, @parentId, @text
                END
            CLOSE my_cursor;
            DEALLOCATE my_cursor;
        END

        RETURN
    END
    GO

    Monday, June 07, 2010 1:07 PM
  • i have a parent and child relation to n level how to get the data relation.

     

    taskid parentid

    1905       1936

    1910       1905

    1911       1905

    1984       1982

    1985       1982

    1986       1964

    1987       1986

    1988       1986

    1993       1986

    1994       1966

    1995       1966

    2013       1966

    2014       1966

    2018       1975

    2019       1975

    2020       1936

    2021       2020

    2022       2020

    2027       1975

    2028       1975

    2034       2036

    2035       2036

    2036       1936

    2037       1986

    2038       1986

    2039       1986

    2040       1936

    2041       2040

    2042       2040

    2100       2081

    2101       2100

    2102       2100

    2103       2081

    2104       2103

    2105       2103

    2106       1936

    2107       2106

    2108       2106

    2109       2056

    2110       2056

    2111       2056

    2112       2056

    2113       2056

    2115       1936

    2116       2115

    2117       2115

    2172       1936

    2173       2172

    2230       2228

    2231       2081

    2232       2081

    2233       1936

    2234       2233

    2235       2233

    2238       1936

    2239       2238

    2240       2238

    2245       1936

    2246       2245

    2247       2245

    2287       2285

    2288       2070

    2299       2271

    2303       2271

    2304       1936

    2305       2304

    2306       2304

    2349       2348

    2350       2348

    2043       1936

    2044       2043

    2045       2043

    2046       1986

    2047       1966

    2048       1966

    2056       1962

    2057       1936

    2058       1936

    2059       2058

    2060       2058

    2061       1986

    2118       1936

    2119       2118

    2120       2118

    2121       1936

    2122       2121

    2123       2121

    2124       2113

    2125       2113

    2126       2113

    2127       2113

    2128       2113

    2129       2113

    2135       2081

    2136       2135

    2196       1936

    2197       2196

    2198       2196

    2199       2070

    2200       1936

    2201       2200

    2202       2200

    2203       1936

    2204       2203

    2205       2203

    1941       1940

    1942       1941

    1943       1941

    1944       1941

    1948       1941

    1949       1941

    1951       1941

    1952       1941

    1953       1944

    1954       1944

    1955       1944

    1956       1944

    1957       1944

    1958       1944

    1959       1944

    1960       1944

    2081       2080

    2082       2081

    2083       2082

    2084       2082

    2085       2081

    2086       2085

    2087       2085

    2091       1534

    2092       1936

    2097       2092

    2098       2092

    2174       2172

    2178       1936

    2179       2178

    2180       2178

    2181       2113

    2187       1936

    2188       2187

    2189       2187

    2190       1936

    2191       2190

    2192       2190

    2253       1936

    2254       2253

    2255       2253

    2256       1936

    2257       2256

    2260       1936

    2261       2260

    2262       2260

    2264       1936

    2265       2264

    2266       2264

    2267       2256

    2332       2271

    2333       2271

    2334       2354

    2335       2334

    2336       2334

    2344       2300

    2348       2081

    3119       1568

    1933       1936

    1934       1933

    1935       1933

    1936       10884

    1937       1936

    1938       1937

    1939       1937

    1940       13939

    1962       13937

    1964       1962

    1966       1962

    1968       1964

    1969       1964

    1970       1962

    1971       1970

    1972       1970

    1973       1975

    1974       1975

    1975       1970

    1976       1975

    1979       1936

    1980       1979

    1981       1979

    1982       1936

    2062       1936

    2063       2062

    2064       2062

    2065       1944

    2066       1944

    2067       1936

    2068       2067

    2069       2067

    2070       1966

    2071       2070

    2072       2070

    2073       2070

    2074       1936

    2075       2074

    2076       2074

    2077       1936

    2078       2077

    2079       2077

    2080       1477

    2137       2135

    2138       2070

    2139       1936

    2140       2139

    2141       2139

    2142       1936

    2143       2142

    2144       2142

    2153       1936

    2154       2153

    2155       2153

    2212       1936

    2213       2212

    2214       2212

    2217       1936

    2218       2217

    2219       2217

    2222       1936

    2223       2222

    2224       2222

    2225       2070

    2227       2222

    2228       1936

    2229       2228

    2268       1962

    2269       2268

    2270       2268

    2271       2268

    2279       1936

    2280       2279

    2281       2279

    2282       1936

    2283       2282

    2284       2282

    2285       1936

    2286       2285

    2384       2348

    2385       2348


    Please Mark the answers and give points it will help users Thanks & Regards, Kamal Agarwala
    Wednesday, October 06, 2010 9:25 AM