sql - Getting lowest level in a tree from any higher level with a self-join -

using sql server 2012, have 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 parent type/code combinations child type/code combinations. maps type 2 type 4, 4 type 6 , 6 type 7. if records parent_type of 2 , parent_code of 1:

select * mytable parent_type = 2 , parent_code = 1 

it return:

type | code  | parent_type | parent_code   4  | 123   |     2       |     1   4  | 234   |     2       |     1 

what i'm trying figure out best way of type 7 codes live under ultimate parent type 1 code. in addition, need work type level. i'd able type 7 codes under type 4 code, or type 7 code (which returns single matching row).

so i'd see, when searching parent_type = 2 , parent_code = 1 is:

type | code  | parent_type | parent_code   7  | 12345 |     6       |     1234   7  | 23456 |     6       |     1234   7  | 34567 |     6       |     2345 

but if i'd started @ parent_type = 4 , parent_code = 123

type | code  | parent_type | parent_code   7  | 12345 |     6       |     1234   7  | 23456 |     6       |     1234 

i tried this:

select grandchild.type        grandchild.code        parent.parent_type        parent.parent_code mytable parent join mytable child on parent.code = child.parent_code join mytable grandchild on child.code = grandchild.parent_code parent.parent.code = 1 

which works great if start type 2 code, fails (returns no results) @ other level.

so search bit , came on question , answer: https://stackoverflow.com/a/1757302/1250301

and tried this:

with q (     select *      mytable     parent_type = 2     union     select m.*     mytable m     join q     on m.parent_code = q.code ) select * q parent_code = 1 

but still works on 1 level returning same result initial select query.

how can select level 7 codes starting higher level code?

note, ultimate goal join result table of type/code combinations (with potentially mixed types) convert list of level 7 codes (which gets joined table has data need, @ level 7 code level).

this works:

declare @parent_type int, @parent_code int set @parent_type = 2 set @parent_code = 1;  cte (     select *     yourtable     (parent_type = @parent_type , parent_code = @parent_code) or (type = @parent_type , code = @parent_code)     union     select b.*     cte     inner join yourtable b         on a.code = b.parent_code ) select * cte type = 7; 

here sqlfiddle demo of this.

the results parent_type = 2 , parent_code = 1 are:

╔══════╦═══════╦═════════════╦═════════════╗ ║ type ║ code  ║ parent_type ║ parent_code ║ ╠══════╬═══════╬═════════════╬═════════════╣ ║    7 ║ 34567 ║           6 ║        2345 ║ ║    7 ║ 12345 ║           6 ║        1234 ║ ║    7 ║ 23456 ║           6 ║        1234 ║ ╚══════╩═══════╩═════════════╩═════════════╝ 

and parent_type = 4 , parent_code = 123:

╔══════╦═══════╦═════════════╦═════════════╗ ║ type ║ code  ║ parent_type ║ parent_code ║ ╠══════╬═══════╬═════════════╬═════════════╣ ║    7 ║ 12345 ║           6 ║        1234 ║ ║    7 ║ 23456 ║           6 ║        1234 ║ ╚══════╩═══════╩═════════════╩═════════════╝