0

I have a table in my SQL Server database with data like this:

ID  ParentID
--- ---------
1   NULL
2   1
3   1
5   1
5   4
5   6
8   6
7   9
9   10

I need a select statement that will return all the rows that have some relation between them (no matter parent or child). For example for id = 1 I need to get all the rows except last two. The same result must be for id = 2, 3, 4, 5, 6 ,8. And for id = 7 or 9 the statement must return only the last two rows. I tried to solve this issue with recursive CTE but I failed.

  • How did you try with a recursive CTE? Can you show your code example? – Mark Sinkinson Aug 20 '15 at 15:46
  • also explain if you mean to have duplicate id rows? Why are there three rows with ID = 5? How do the second and third rows with 5 as id relate to id 1? How does the row of id 8 relate to 1? It's parent is 6 but there is no ID = 6. – paulbarbin Aug 20 '15 at 17:25
  • my recursive CTE was like this ;with rec AS( select ID, ParentID FROM MyTable where ID = ? UNION ALL select a.id, a.parentid FROM Mytable a join rec b on a.parentID = b.ID ) select * from rec. The second and the third rows with id = 5 related with id 1, because there are row with ID = 5, and parentID = 1... This means that this two IDs are related to each other. For the same reason we have rows with id = 5 and parentid = 6, this means that 5 and 6 are also related (for the same reason are related 6 and 8, and thats why id 8 is related with id 1). – Artashes Khachatryan Aug 21 '15 at 6:36
  • @ArtashesKhachatryan: you should add that extra detail directly to the question - it'll be easier to read and won't get lost if the amount of comments grow to the point where some are hidden by default. You should see an "edit" link at the bottom of the question, just below the tags list. – David Spillett Aug 21 '15 at 17:03
0

I'm not great with recursion either, so don't feel bad. I've had success with structures similar to the following. It's not elegant, but you can see exactly what's going on. Basically you need to gather all the possible values to evaluate against both ID and ParentID for a given ParentID value. This solution assumes integer values. It will get a little more tricky with alphanumric values. The nice thing about this is you can run it as is or modify it to display a hierarchical ordering.

    --create the table for the example
    IF OBJECT_ID('tbl_RecursiveExample','U') IS NOT NULL
        DROP TABLE tbl_RecursiveExample

    CREATE TABLE tbl_RecursiveExample
    (
          ID INT
        , ParentID INT
    )

    INSERT tbl_RecursiveExample
    VALUES   (1,NULL)
           , (2,1)
           , (3,1)
           , (5,1)
           , (5,4)
           , (5,6)
           , (8,6)
           , (7,9)
           , (9,10);


    --change the value to the parentID you are interested in
    DECLARE @ParentIDVal INT=10;

    WITH 
    cteFirstRelationship --Top level relationships
    AS
    (
        SELECT ID RelatedIDs
        FROM tbl_RecursiveExample 
        WHERE ParentID = @ParentIDVal
        UNION
        SELECT ParentID
        FROM tbl_RecursiveExample
        WHERE ID = @ParentIDVal
    ),
    cteSecondRelationship --secondary relationships
    AS
    (
        SELECT ID RelatedIDs
        FROM tbl_RecursiveExample t
            JOIN cteFirstRelationship cf ON cf.RelatedIDs=t.ParentID
        UNION
        SELECT ParentID
        FROM tbl_RecursiveExample t
            JOIN cteFirstRelationship cf ON cf.RelatedIDs=t.ID
    )
    --possible add another level if needed
    SELECT DISTINCT t.* --> Needed due to the match of both values
    FROM tbl_RecursiveExample t
        JOIN
        (       
            SELECT RelatedIDs from cteFirstRelationship
            UNION
            SELECT RelatedIDs from cteSecondRelationship
        )r ON r.RelatedIDs = t.ID OR r.RelatedIDs = t.ParentID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.