Child and parent Relation

  • I have a table like this

    Id ParentId

    1 NULL

    2 NULL

    3NULL

    41

    51

    62

    72

    83

    93

    I need the ordering in result set like this

    IdParentId

    1NULL

    41

    51

    2 NULL

    62

    72

    3NULL

    83

    93

    Please do the favour

  • Sorry difficult play :-P, but desperate to see the reply

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • I think this does what you need.

    --test table

    CREATE TABLE OrderTest

    (id INT,

    parentid INT NULL)

    --test data

    INSERT INTO OrderTest

    VALUES

    (1, NULL),

    (2, NULL),

    (3, NULL),

    (4, 1),

    (5, 1),

    (6, 2),

    (7, 2),

    (8, 3),

    (9, 3)

    --query

    SELECT *

    FROM OrderTest

    ORDER BY COALESCE(ParentId,Id), Id

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (8/10/2010)


    I think this does what you need.

    --test table

    CREATE TABLE OrderTest

    (id INT,

    parentid INT NULL)

    --test data

    INSERT INTO OrderTest

    VALUES

    (1, NULL),

    (2, NULL),

    (3, NULL),

    (4, 1),

    (5, 1),

    (6, 2),

    (7, 2),

    (8, 3),

    (9, 3)

    --query

    SELECT *

    FROM OrderTest

    ORDER BY COALESCE(ParentId,Id), Id

    Ahh 🙂 grant .AWESOME play with COALESCE function.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Probably would have worked with ISNULL too, but COALESCE came into my head first.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes its working with ISNULL

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Thanks Bhuvanesh... I am glad to you for helping out me

  • Thanks Fritchey... I am glad to you for helping out me

  • CELKO (8/11/2010)


    You might want to look at the Nested Sets model for this. Life will be better.

    Any example or link ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • It seems to me that this solution will only work if there are only two levels of parent/child relationship. Do you have cases where the relationships go to three or more levels (i.e. 10's parent is 4, 11's parent is 10, etc)?

  • Hi,

    I have two queries, say for example

    Select distinct 0 parent_id, ROW_NUMBER() OVER(

    ORDER BY Column2_Desc ) AS 'ID', Column2_Desc

    From table1

    Union

    Select distinct 0 parent_id, ROW_NUMBER() OVER(

    ORDER BY Column2_Desc ) AS 'ID', Column2_Desc

    From table2

    I don’t have relationship between two tables.But I want to frame parent child relationship between these two so that my resultset should like

    Parent_ididColumn2_Desc

    01ABC

    02XYZ

    03ASDF

    14VGTY

    25DFT

    I don’t want to use stored procedure.

    Kindly Help Me Out

  • brindaam (12/13/2010)


    Hi,

    I have two queries, say for example

    Select distinct 0 parent_id, ROW_NUMBER() OVER(

    ORDER BY Column2_Desc ) AS 'ID', Column2_Desc

    From table1

    Union

    Select distinct 0 parent_id, ROW_NUMBER() OVER(

    ORDER BY Column2_Desc ) AS 'ID', Column2_Desc

    From table2

    I don’t have relationship between two tables.But I want to frame parent child relationship between these two so that my resultset should like

    Parent_ididColumn2_Desc

    01ABC

    02XYZ

    03ASDF

    14VGTY

    25DFT

    I don’t want to use stored procedure.

    Kindly Help Me Out

    it seems another/new query.please start these with NEW POST so that you can get better results ans people dont get confused with post's heading and internal stuff like this

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • CREATE TABLE OrderTest

    (id INT,

    parentid INT NULL)

    --test data

    INSERT INTO OrderTest VALUES (1, NULL)

    INSERT INTO OrderTest VALUES (2, NULL)

    INSERT INTO OrderTest VALUES (3, NULL)

    INSERT INTO OrderTest VALUES (4, 1)

    INSERT INTO OrderTest VALUES (5, 1)

    INSERT INTO OrderTest VALUES (6, 2)

    INSERT INTO OrderTest VALUES (7, 2)

    INSERT INTO OrderTest VALUES (8, 3)

    INSERT INTO OrderTest VALUES (9, 3)

    INSERT INTO OrderTest VALUES (10,6)

    WITH Hierarchy (Parentid,ID, lvl,sort) AS

    (SELECT ParentiD, id,0, cast(isnull( id,0) as varchar(1024))

    FROM OrderTest

    WHERE ParentiD IS null

    UNION ALL

    SELECT ot.ParentiD, ot.ID, lvl+1,cast(sort+'|'+cast(lvl as varchar(5))+'|'+cast(ot.parentid as varchar(5)) as varchar(1024))

    FROM OrderTest ot INNER JOIN hierarchy h ON ot.ParentiD = h.ID

    )

    select ParentiD,ID,lvl

    from Hierarchy

    order by sort

    Try this i hope this is what you are expecting.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply