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

Question

Using sql server 2012, I have a table that looks something like 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 to child type/code combinations. It maps type 2 to type 4, 4 to type 6 and 6 to type 7. If I look for all the records with a Parent_Type of 2 and a Parent_Code of 1:

select * from mytable where parent_type = 2 and parent_code = 1

It will return:

Type | Code  | Parent_Type | Parent_Code
  4  | 123   |     2       |     1
  4  | 234   |     2       |     1

What I'm trying to do is figure out the best way to get all of the type 7 codes that live under the ultimate parent type 1 code. In addition, I need this to work for any type level. So I'd like to be able to get the type 7 codes under a type 4 code, or even with a type 7 code (which just returns the single matching row).

So what I'd like to see, when searching for parent_type = 2 and 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 at parent_type = 4 and parent_code = 123

Type | Code  | Parent_Type | Parent_Code
  7  | 12345 |     6       |     1234
  7  | 23456 |     6       |     1234

I initially tried this:

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

Which works great if you start with a type 2 code, but fails (returns no results) at any other level.

So I search about a bit and came on this question and answer: https://stackoverflow.com/a/1757302/1250301

And tried this:

with q as
(
    select * 
    from mytable
    where parent_type = 2
    union all
    select m.*
    from mytable m
    join q
    on m.parent_code = q.code
)
Select *
from q where parent_code = 1

But still only works on one level returning the same result as my initial select query.

How can I get this to select all the level 7 codes starting with any higher level code?

Note, my ultimate goal is to join this result with another table of type/code combinations (with potentially mixed types) to convert it to a list of all level 7 codes (which then gets joined with another table that has the data I need, but only at the level 7 code level).

I kinda got lost in the word problem. Can you post a sample of the desired result for a sample input? — Lamak, Sep 10 '14 at 14:48
@Lamak: Ok - tried to make it a little clearly with a couple of expected outputs start from two different points. — Matt Burland, Sep 10 '14 at 14:53
Have you had a look at HierarchyId, specifically adjacency list pattern? — GregD, Sep 10 '14 at 14:56

score 2 · Accepted Answer · edited Sep 10 '14 at 15:11

This works:

DECLARE @parent_type INT, @parent_code INT
SET @parent_type = 2
SET @parent_code = 1;

WITH CTE AS
(
    SELECT *
    FROM YourTable
    WHERE (parent_type = @parent_type AND parent_code = @parent_code) OR (type = @parent_type AND code = @parent_code)
    UNION ALL
    SELECT B.*
    FROM CTE A
    INNER JOIN YourTable B
        ON A.Code = B.Parent_Code
)
SELECT *
FROM CTE
WHERE Type = 7;

Here is a sqlfiddle with a demo of this.

The results for parent_type = 2 and parent_code = 1 are:

╔══════╦═══════╦═════════════╦═════════════╗
║ Type ║ Code  ║ Parent_Type ║ Parent_Code ║
╠══════╬═══════╬═════════════╬═════════════╣
║    7 ║ 34567 ║           6 ║        2345 ║
║    7 ║ 12345 ║           6 ║        1234 ║
║    7 ║ 23456 ║           6 ║        1234 ║
╚══════╩═══════╩═════════════╩═════════════╝

And for parent_type = 4 and parent_code = 123:

╔══════╦═══════╦═════════════╦═════════════╗
║ Type ║ Code  ║ Parent_Type ║ Parent_Code ║
╠══════╬═══════╬═════════════╬═════════════╣
║    7 ║ 12345 ║           6 ║        1234 ║
║    7 ║ 23456 ║           6 ║        1234 ║
╚══════╩═══════╩═════════════╩═════════════╝

Thanks. That actually works great with one exception (and maybe I should have highlighted this in the question a bit better). If you start with a `type = 7` code, it'll return no results. I would like it to return just the one matching result. But I can probably figure that out myself or else work around it. — Matt Burland, Sep 10 '14 at 15:05
Ok - I edited to add an `or` in the `where` clause to fix the `type=7` issue. Hope you don't mind. — Matt Burland, Sep 10 '14 at 15:12

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

1 Answers1

Linked