Finding related records within the same table

  • sku370870 (8/8/2016)


    drew.allen (8/5/2016)


    sku370870 (8/5/2016)


    drew.allen (8/5/2016)


    sku370870 (8/5/2016)


    Thanks for your reply. But when I am on any particular stage, all I have to hand is the StakeholderID for that stage - so I can't start with the original stakeholder - I have to 'look up the table' to find the first stakeholder. I was hoping there was some way of doing the cte where you could go find the Stakeholders going up and then down - as sorting them would not be a problem as I have a DateAdded field I can sort on (as Stages may not be sequential as they can be added out of order by users if they need to do the same stage twice).

    Original stakeholders have StakeholderWasID = 0.

    Drew

    Yes, but if I have a StakeholderID of, say, 4 - I don't know which StakeholderID that has a StakeholderWasID of zero is the 'parent' of StakeholderID 4 - without working up the table to find out.

    Look, I know it can be done, because I wrote a query that does it. I'm trying to show you the path to take to get there, without handing it to you on a platter, but you refuse to even consider what I've said.

    Drew

    I'm sorry, but I don't understand how to establish which is the parent with a StakeholderWasID of zero - without using the cte that I posted. If you do, I'd be very pleased to see it.

    This may not be how Jeff would solve this problem, but I came up with something that solves the problem based on your sample data.

    declare @TargetStakeHolder int;

    set @TargetStakeHolder = 4;

    with Stakeholders1 as (

    select

    StakeholderID,

    StakeholderWasID

    from

    #tblStakeHolders

    where

    StakeholderID = @TargetStakeHolder

    union all

    select

    tsh.StakeholderID,

    tsh.StakeholderWasID

    from

    #tblStakeholders tsh

    inner join Stakeholders1 sh1

    on (sh1.StakeholderID = tsh.StakeholderWasID)

    ), Stakeholders2 as (

    select

    StakeholderID,

    StakeholderWasID

    from

    #tblStakeHolders

    where

    StakeholderID = @TargetStakeHolder

    union all

    select

    tsh.StakeholderID,

    tsh.StakeholderWasID

    from

    #tblStakeholders tsh

    inner join Stakeholders2 sh2

    on (sh2.StakeholderWasID = tsh.StakeholderID)

    ), TargetStakeHolders as (

    select StakeholderID from Stakeholders1

    union

    select StakeholderID from Stakeholders2

    )

    select

    *

    from

    TargetStakeHolders tsh

    inner join #tblStakeholdersNotes tsn

    on tsh.StakeholderID = tsn.StakeholderID;

    -- OR

    with Stakeholders1 as (

    select

    StakeholderID,

    StakeholderWasID

    from

    #tblStakeHolders

    where

    StakeholderID = @TargetStakeHolder

    union all

    select

    tsh.StakeholderID,

    tsh.StakeholderWasID

    from

    #tblStakeholders tsh

    inner join Stakeholders1 sh1

    on (sh1.StakeholderID = tsh.StakeholderWasID)

    ), Stakeholders2 as (

    select

    StakeholderID,

    StakeholderWasID

    from

    #tblStakeHolders

    where

    StakeholderID = @TargetStakeHolder

    union all

    select

    tsh.StakeholderID,

    tsh.StakeholderWasID

    from

    #tblStakeholders tsh

    inner join Stakeholders2 sh2

    on (sh2.StakeholderWasID = tsh.StakeholderID)

    ), TargetStakeHolders as (

    select StakeholderID from Stakeholders1

    union all

    select StakeholderID from Stakeholders2

    )

    select

    *

    from

    #tblStakeholdersNotes tsn

    where

    exists(select 1 from TargetStakeHolders tsh where tsh.StakeholderID = tsn.StakeholderID);

    Personally, I like the second option I posted.

  • Here are the two queries that I was alluding to. The first one starts with the original stakeholders and moves down the tree. Then you filter based on records having the same original stakeholder as the record that you are interested in.

    ;

    WITH rCTE(OriginalStakeHolderID, StakeholderID, StageID, StakeholderWasID) AS (

    SELECT StakeholderID AS OriginalStakeHolderID, StakeholderID, StageID, StakeholderWasID

    FROM #tblStakeholders

    WHERE StakeholderWasID = 0

    UNION ALL

    SELECT c.OriginalStakeHolderID, e.StakeholderID, e.StageID, e.StakeholderWasID

    FROM #tblStakeholders e

    INNER JOIN rCTE c ON e.StakeholderWasID = c.StakeholderID

    )

    SELECT *

    FROM rCTE

    INNER JOIN #tblStakeholdersNotes tsn

    ON rCTE.StakeholderID = tsn.StakeholderID

    WHERE rCTE.OriginalStakeHolderID IN (

    SELECT OriginalStakeHolderID

    FROM rCTE AS filter

    WHERE filter.StakeholderID = 4

    )

    ;

    The second approach, which uses fewer reads when you are looking for a specific stakeholder, but probably uses more reads if you need a group of stakeholders, first moves up the tree to find the original stakeholder for the record of interest, and then moves down the tree from that stakeholder.

    ;

    WITH rCTE_up( StakeholderID, StageID, StakeholderWasID ) AS (

    SELECT s.StakeholderID, s.StageID, s.StakeholderWasID

    FROM #tblStakeholders s

    WHERE s.StakeholderID = 4

    UNION ALL

    SELECT p.StakeholderID, p.StageID, p.StakeholderWasID

    FROM #tblStakeholders p

    INNER JOIN rCTE_up AS c

    ON p.StakeholderID = c.StakeholderWasID

    )

    , rCTE_down( StakeholderID, StageID ) AS (

    SELECT rCTE_up.StakeholderID, rCTE_up.StageID

    FROM rCTE_up

    WHERE rCTE_up.StakeholderWasID = 0

    UNION ALL

    SELECT ts.StakeholderID, ts.StageID

    FROM #tblStakeholders ts

    INNER JOIN rCTE_down

    ON ts.StakeholderWasID = rCTE_down.StakeholderID

    )

    SELECT *

    FROM rCTE_down

    INNER JOIN #tblStakeholdersNotes tsn

    ON rCTE_down.StakeholderID = tsn.StakeholderID

    ;

    I haven't had a chance to review the other solutions posted to see how they compare.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Okay, after looking at the other code posted, it looks like Lynn's code will work only with paths. My code will work with all directed acyclic graphs. A path has at most one parent and one child, a directed acyclic graph can have any number of parents and children as long as there are no cycles (or loops). The sample data provided shows paths, but it's not clear whether that is a property of the data as a whole or just the subset chosen as the sample.

    The reason that you were having problems is that you were treating it as an undirected graph, which means that you were introducing loops. rCTEs have troubles when you introduce loops.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Hey Jeff, are you going to use your Hierarchies on Steroids approach? 🙂

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • drew.allen (8/8/2016)


    Here are the two queries that I was alluding to.

    Thanks for taking the time to post that, Drew. I appreciate it very much.

    Lynn Pettis (8/8/2016)


    I came up with something that solves the problem based on your sample data

    You too, Lynn. Not many folks post for hierarchical problems.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The Dixie Flatline (8/8/2016)


    Hey Jeff, are you going to use your Hierarchies on Steroids approach? 🙂

    That's what I was thinking. Gotta look at what's been posted a bit more.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 16 through 20 (of 20 total)

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