recursive query with peer relations

Question

Let's say there is a table of relationships (entity_id, relationship, related_id)

1, A, 2     
1, A, 3      
3, B, 5 
1, C, null 
12, C, 1 
100, C, null

I need a query that will pull all related rows. For example, if i queried for entity_id = 1, the following rows should be pulled

1, A, 2     
1, A, 3      
3, B, 5 
1, C, null 
12, C, 1 

Actually, if i queried for entity_id = 1, 2, 3, 5, or 12, the resultset should be the same.

This is different than the standard manager-employee paradigm as there is no hierarchy. The relationships can go in any direction.

---

EDIT None of the answers posted thus far worked.

I was able to come up with a solution that works.

I'll give the solution credit to the one who can clean this monstrosity into something more elegant.

with tab as ( 
-- union for reversals
 select id, entity_id, r.related_id, 1 level
 , cast('/' + cast(entity_id as varchar(1000)) + '/'  as varchar(1000)) path 
  from _entity_relation r 
  where not exists(select null from _entity_relation r2 where r2.related_id=r.entity_id) 
    or r.related_id is null 
 union
 select id, related_id, r.entity_id, 1 level
 , cast('/' + cast(related_id as varchar(1000)) + '/' as varchar(1000)) path 
  from _entity_relation r 
  where not exists(select null from _entity_relation r2 where r2.related_id=r.entity_id) 
    or r.related_id is null 

-- create recursive path
union all 
 select r.id, r.entity_id, r.related_id, tab.level+1
 , cast(tab.path + '/' + cast(r.entity_id as varchar(100)) + '/' + '/' + cast(r.related_id as varchar(1000)) + '/' as varchar(1000)) path 
  from _entity_relation r 
  join tab 
  on tab.related_id = r.entity_id   
) 

select x.id
    , x.entity_id
    ,pr.description as relation_description
    ,pt.first_name + coalesce(' ' + pt.middle_name,'') + ' ' + pt.last_name as relation_name
    ,CONVERT(CHAR(10), pt.birth_date, 101) as relation_birth_date   
from (

select entity_id, MAX(id) as id from (
select distinct tab.id, entity_id
from tab 
join( 
    select path 
    from tab  
    where entity_id=@in_entity_id
) p on p.path like tab.path + '%' or tab.path like p.path + '%'
union
select distinct tab.id, related_id
from tab 
join( 
    select path 
    from tab  
    where entity_id=@in_entity_id
) p on p.path like tab.path + '%' or tab.path like p.path + '%'
union
select distinct tab.id, entity_id
from tab 
join( 
    select path 
    from tab  
    where related_id=@in_entity_id
) p on p.path like tab.path + '%' or tab.path like p.path + '%'
union
select distinct tab.id, related_id
from tab 
join( 
    select path 
    from tab  
    where related_id=@in_entity_id
) p on p.path like tab.path + '%' or tab.path like p.path + '%'
) y
group by entity_id
) x
join _entity_relation pr on pr.id = x.id
join _entity pt on pt.id = x.entity_id
where x.entity_id <> @in_entity_id;

Answer 1

Please be careful with you data as to accomplish your task you must avoid circular references. The following query can be optimized but for sure it'll work

;with tab as (
 select entity_id, relationship, related_id, 1 level, cast('/' + cast(entity_id as varchar(1000)) as varchar(1000)) path
  from #r r
  where not exists(select null from #r r2 where r2.related_id=r.entity_id)
    or r.related_id is null
union all
 select r.entity_id, r.relationship, r.related_id, tab.level+1, cast(tab.path + '/' + cast(r.entity_id as varchar(100)) as varchar(1000)) path
  from #r r
  join tab
  on tab.related_id = r.entity_id  
)
select distinct tab.* 
    from tab
    join(
select path
    from tab 
    where entity_id=1) p
    on p.path like tab.path + '%' or tab.path like p.path + '%'