T-SQL Query to Pull a Parent's Tags and all Children's Tags

Question

Backgroud

I have a DB Schema in SQL Server that looks like the following:

http://sqlfiddle.com/#!18/dc3cf/3

| id    | tag   | child_id  |
|----   |-----  |---------- |
| 1     | A     |           |
| 1     |       | 4         |
| 2     | C     |           |
| 3     | C     |           |
| 4     | B     |           |
| 4     |       | 5         |
| 5     | D     |           |
| 5     | E     |           |

Each 'id' record may have a child (which is stored in the same table). Each child may have any number of sub children. I won't know the number of hierarchy levels but it will probably be no more than 10 levels deep.

In the example, there are:

• 3 root elements: id's 1, 2, 3
• 1 child and 1 child's child of id 1: id's 4 and 5 (respectively)

Question

I need to be able to query to get a result of all of an id's tags including all of it's children's tags. For example, I would need my output to be the following based on the table data above:

| id    | tag   |
|----   |-----  |
| 1     | A     |
| 1     | B     |
| 1     | D     |
| 1     | E     |
| 2     | C     |
| 3     | C     |
| 4     | B     |
| 5     | D     |
| 5     | E     |

Note that I need the children to still appear.

Is this possible without joining the table to itself 'n' many times where 'n' is the number of hierarchy levels?

Edit

To clarify, each id is also a root elements. So the only way to know if an ID is also a child is to look and see if the id has another record where it has a child_id. I made another version of the SQL Fiddle that demonstrates this point. Note that id 2 now has a child: http://sqlfiddle.com/#!18/422f9/1

| id    | tag   | child_id  |
|----   |-----  |---------- |
| 1     | A     |           |
| 1     |       | 4         |
| 2     | C     |           |
| 2     |       | 5         |
| 3     | C     |           |
| 4     | B     |           |
| 4     |       | 5         |
| 5     | D     |           |
| 5     | E     |           |

Answer 1

Yes, it is possible without joining the table n-times, you can use a CTE (Common Table Expression), see Docs

In your situation the code should be something like:

WITH cte (id, tag, child_id, parent) AS
(
    SELECT id, tag, child_id, id
    FROM demo
    WHERE id NOT IN(SELECT child_id FROM demo WHERE child_id IS NOT NULL)
    UNION ALL
    SELECT demo.id, demo.tag, demo.child_id, cte.parent
    FROM demo
        JOIN cte
            ON cte.child_id = demo.id
)
SELECT parent, tag
FROM cte
WHERE tag IS NOT NULL
UNION 
SELECT id, tag
FROM demo
WHERE tag IS NOT NULL
ORDER BY parent, tag

Edit: Determine base elements dynamically.