Home Forums SQL Server 2008 SQL Server 2008 - General SQL query to get all predecessors and successors for given node. RE: SQL query to get all predecessors and successors for given node.

  • September 4, 2012 at 3:38 pm

    #1533554

    With the data you have provided, you will get 'duplicates' because there are different ways to end up at 603 from 602. Some paths use 2 steps, some 3 and some 4, so it is a valid path at all three hierachies.

    Try the following:

    DML and data seed

    create table sandyha

    (

    ContractDateID int,

    PredecessorContractDateID int

    )

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (570 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (572 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (574 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (576 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (578 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (580 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (582 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (584 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (586 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (588 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (596 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (604 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (605 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (606 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (607 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (608 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (609 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (610 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (611 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (612 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (613 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (614 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (615 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (616 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (617 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (618 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (619 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (620 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (621 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (622 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (623 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (624 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (625 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (626 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (627 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (630 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (601 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (590 ,NULL)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (597 ,570)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (598 ,570)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,570)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (571 ,570)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (573 ,572)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,572)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (599 ,574)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (575 ,574)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (577 ,576)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,576)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (595 ,576)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (602 ,578)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (579 ,578)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (581 ,580)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (602 ,580)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (602 ,582)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (583 ,582)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (585 ,584)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,584)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,586)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (587 ,586)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (589 ,588)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (599 ,588)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (592 ,590)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (591 ,590)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (593 ,592)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (594 ,592)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (599 ,592)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (600 ,599)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (602 ,599)

    insert into sandyha (ContractDateID, PredecessorContractDateID) values (603 ,602)

    CTE query

    with CTE as

    (

    SELECT

    s.ContractDateID,

    s.PredecessorContractDateID,

    1 as level,

    cast(cast(coalesce(s.PredecessorContractDateID,'') as nvarchar(5)) as nvarchar(255)) as DHPath

    FROM

    sandyha s

    WHERE

    s.PredecessorContractDateID is null

    UNION ALL

    SELECT

    s.ContractDateID,

    s.PredecessorContractDateID,

    (x.level +1) as level,

    cast(x.DHPath + '-' + cast(coalesce(s.PredecessorContractDateID,'') as nvarchar(5)) as nvarchar(255)) as DHPath

    FROM

    sandyha s

    join

    cte x on x.ContractDateID = s.PredecessorContractDateID

    WHERE

    s.PredecessorContractDateID is not null

    )

    select distinct * from CTE order by contractdateID,level

    you will get the following results set (partial shown)

    ConPrevLvlPath

    60360230-578-602

    60360230-580-602

    60360230-582-602

    60360240-574-599-602

    60360240-588-599-602

    60360250-570-592-599-602

    60360250-572-592-599-602

    60360250-576-592-599-602

    60360250-584-592-599-602

    60360250-586-592-599-602

    60360250-590-592-599-602

    So you can get to 603 through 3, 4 or 5 predecessors, depending on where you start.

    What is the business problem you are trying to solve?