1

I have the following Table with two columns. 

Parent  Child
A       C
A       D
B       A
B       E
C       H
J       C
F       G
G       I

So I need to pass in a Parent of A and I am supposed to get the following back- All of a's Parent's and children but Also all of the Parents and Children to those associated to A (either parent or children) I need all their parents and children and so on.

SO in the example of A being passed to the proc I would get the following

A   C
A   D
B   A
B   E
C   H
J   C

If F was Passed I would just get 

F   G
G   I
MicroMan
  • 1,988
  • 4
  • 32
  • 58
  • 2
    possible duplicate of [SQL recursive function that gets all ancestors of an item](http://stackoverflow.com/questions/16749095/sql-recursive-function-that-gets-all-ancestors-of-an-item) – rutter May 29 '15 at 20:59

2 Answers2

1
select * from test 
where parent in (
    select parent from test
    where parent = 'F' or child = 'F'

    union 
    select child from test
    where child = 'F' or parent = 'F')

or child in (
    select parent from test
    where parent = 'F' or child = 'F'

    union 
    select child from test
    where child = 'F' or parent = 'F');
J Petersen
  • 162
  • 1
  • 13
1

Try this, it is similar to the link given in the comments but accounts for both parent and child recursion. Hopefully it will work for you.

WITH relationships AS (
   SELECT *
   FROM Table
   WHERE Child = 'A'
   UNION ALL
   SELECT p.*
   FROM Table p
   JOIN relationships pa on pa.Child = p.Parent
   UNION ALL
   SELECT c.*
   FROM Table c
   JOIN relationships ch on ch.Parent = c.Child
) 
select *
from name_tree;
naw103
  • 1,843
  • 1
  • 15
  • 14