Filtering SQL Tree Query

if I have a tree query like the one below and I want to filter the Name = 'Son' and also select all of its parent records and so the result should yield the first 3 rows. How would I construct my query? I've read that I should use Common Table Expression (CTE) but I'm a newbie on CTE. Can anyone help me? 开发者_运维技巧Thanks.

select 1 AS id, NULL AS parent, 'God' AS name
    UNION 
    select 2 AS id, 1 AS parent, 'Father' AS name
    UNION 
    select 3 AS id, 2 AS parent, 'Son' AS name
    UNION 
    select 4 AS id, NULL AS parent, 'Godmother' AS name
    UNION 
    select 5 AS id, 4 AS parent, 'Mother' AS name

Sounds like you could store the tree in a table (or define a view using the SQL above), and then if you are using Oracle, you could use the CONNECT BY function to filter records.

Is this what you're looking for?

;with SomeCTE as
(
select *
from (
select 1 AS id, NULL AS parent, 'God' AS name     
UNION      
select 2 AS id, 1 AS parent, 'Father' AS name     
UNION      
select 3 AS id, 2 AS parent, 'Son' AS name     
UNION      
select 4 AS id, NULL AS parent, 'Godmother' AS name    
UNION      
select 5 AS id, 4 AS parent, 'Mother' AS name ) as a
)
select *
from SomeCTE a
left join SomeCTE b
on a.parent = b.id
left join SomeCTE c
on b.parent = c.id
where a.name = 'son'