• I think you need to use a recursive CTE in this case, so that all possible relations are created through joining to the data itself .
    if you are not familiar with recursive CTE's Dwain Camps has a decent article here: http://www.sqlservercentral.com/articles/T-SQL/90955/
    your example has only a single parent-child pair, but this would go deeper, if the rest of the data is more hierarchical that the example.


    /*--Results
    Bold_ID  Code    Parentphase
    5144471  1000317_EV  1
    5144535  100317_SUPPORT 5144471
    */
    IF OBJECT_ID('[dbo].[zzzTest]') IS NOT NULL
    DROP TABLE [dbo].[zzzTest]
    GO
    CREATE TABLE zzzTest(
      Bold_ID int,Code varchar(255),Parentphase int)

    INSERT INTO zzzTest
    values ('5144471', '1000317_EV', '1');
    insert into zzztest
    values ('5144535', '100317_SUPPORT', '5144471');

    --get the magically created hierarchy
    ;WITH cte
    AS (
    SELECT Bold_ID, Code,Parentphase FROM zzzTest WHERE Parentphase = 1
    UNION ALL
    SELECT t1.Bold_ID, t1.Code,t1.Parentphase
    FROM zzztest t1
    INNER JOIN cte c1
      ON c1.Bold_ID = t1.Parentphase
    )
     
    SELECT *
    FROM cte
    ORDER BY Parentphase

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!