sql - Flatten Hierarchical Table -

follow here: getting lowest level in tree higher level self-join

i realized asking wrong question. have hierarchical table looks this:

type | code  | parent_type | parent_code   4  | 123   |     2       |     1   4  | 234   |     2       |     1   6  | 1234  |     4       |     123    6  | 2345  |     4       |     234   7  | 12345 |     6       |     1234   7  | 23456 |     6       |     1234   7  | 34567 |     6       |     2345 

it maps "type 2" codes "type 4", "type 4" "type 6" , "type 6" "type 7". previous question (and answer) dealt how select type 7 codes under single parent code. so, example, how type 7 codes under type 2, code 1.

but need join table list of types , codes. example, might have table:

type | code     4  | 123   6  | 7851 

and need level 7 codes under both codes. in other words, (i think) need flatten hierarchy this:

type | code  | parent_type | parent_code   7  | 12345 |     2       |     7   7  | 23456 |     2       |     7   7  | 34567 |     2       |     7   7  | 12345 |     4       |     123   7  | 23456 |     4       |     123   7  | 34567 |     4       |     234   7  | 12345 |     7       |     12345     // note: these last 3 might not    7  | 23456 |     7       |     23456     // strictly needed   7  | 34567 |     7       |     34567  

so like:

 select p.type, p.code myothertable o join mytable p on o.type = p.parent_type   , o.code = p.parent_code 

to try , flatten original table, i've tried variations on answer original question, no had luck. example:

with cte ( select p.type, p.code, p.parent_type, p.parent_code mytable p parent_type = 2  union  select c.type, c.code, c.parent_type, c.parent_code mytable c inner join cte on c.parent_code = cte.code ) select * cte 

does nothing useful other mess ordering of table if i'd directly called:

select * mytable 

sounds recursive cte should trick:

with ctetree (   select     t.type,     t.code,     t.parent_type,     t.parent_code       recordstofind f     inner join tree t     on t.type = f.type     , t.code = f.code    union    select     t.type,     t.code,     t.parent_type,     t.parent_code       ctetree f     inner join tree t     on t.parent_type = f.type     , t.parent_code = f.code       f.type != 7 ) select   type,   code,   parent_type,   parent_code   ctetree   type = 7 ; 

http://sqlfiddle.com/#!3/cc4ee/9