sql server 2008 - SQL query: help needed -

i need sql query, have tried replicate actual problem creating simple example:

parents table:

id   name     parent_id 1   parent1     null 2   parent2     1 3   parent3     1 4   parent4     3 5   parent5     3 6   parent6     5 7   parent7     5 

relatives table:

id    name       parent_id 1   relative1   2 2   relative2   3 3   relative3   4 4   relative4   5 5   relative5   7 

parents table has list of parents have parents themselves. relatives table has list of relatives parent_id column.

how find relatives of parent3 including 'desendants' of parent3, i.e. query should return following relatives table:

relative2 (because parent id 3)

relative3 (because parent id 4 parent id 3)

relative4 (because parent id 5 parent id 3)

relative5 (because parent id 7 parent id 5 parent id 3)

i'm using sql server 2008. hope makes sense, appreciated.

try this

;with cte (     select [id], [name]  parents [name] = 'parent3'     union     select t1.[id], t1.[name] cte c      inner join  parents t1 on c.[id] = t1.[parent_id] )  select * relatives  [parent_id] in (select [id] cte) 

sql fiddle demo