Show all records with a connection of any distance for given ID

user_16343223 0
Viewed 52

I have a table with data similar to the data below:

RecID1 RecID2
1 2
3 1
5 2
8 7
4 5
6 8
1 9

I would like a query that if given an ID of say 1 would return the following rows:

RecID1 RecID2
1 2
3 1
5 2
4 5
1 9

I would like to get the results of any rows of records that related to each other. Since 1 has the child of 2 and 2 has a parent of 5 they would be related even though they both do not have the same parent.

So far this is the code I have come up with and have had no luck in getting the results I am looking for.

    create table testtable (RecID1 int, RecID2 int)

    insert into testtable values (1,2)
    insert into testtable values (3,1)
    insert into testtable values (5,2)
    insert into testtable values (8,7)
    insert into testtable values (4,5)
    insert into testtable values (6,8)
    insert into testtable values (1,9);

WITH parent AS (
        SELECT recid1
        FROM testtable
        WHERE recid1 = 1 or recid2=1
    ), tree AS (
        SELECT x.RecID1, x.RecID2
        FROM testtable x
        INNER JOIN parent ON x.RecID1 = parent.RecID1 or x.RecID2 = parent.RecID1
        UNION ALL
        SELECT y.RecID1, y.RecID2
        FROM testtable y
        INNER JOIN tree t ON y.RecID1 = t.RecID2
    )
    SELECT RecID1, RecID2
    FROM tree group by RecID1,RecID2

Any help would be much appreciated!

sql-server
1 Answers

If an atomic query is not necessary, then when you traverse the tree you can store the nodes you've visited into a table variable. Then when you don't add any more nodes to the table, you're done and you can query testtable for any rows that have a visited node.

Create a table to store the nodes you've visited:

declare @seed int = 1; -- user passed parameter

declare @hits table (value int)
insert @hits select @seed;

Traverse the tree, so long as you keep getting new hits:

while @@rowcount > 0 

    insert      @hits

    select      
    distinct    ap.value
    from        @testtable t
    cross apply (values (t.RecID1),(t.RecID2)) ap (value)
    where       exists (
                    select  0
                    from    @hits h 
                    where   h.value in (t.RecID1, t.RecID2)
                )

    except      -- we only want new hits, or else @@rowcount won't reach 0
    select      value 
    from        @hits;

Output any row that has a visited value:

select      *
from        @testtable t
where       exists (
                select  0
                from    @hits h
                where   h.value in (t.RecID1, t.RecID2)
            );
user_34165510
Related