Select tree-based structure 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.
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.* --&gt; 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