Query for rows including child rows

Question

A few weeks ago, I asked a question about how to generate hierarchical XML from a table, that has a parentID column. It all works fine. The point is, according to the hierarchy, I also want to query a table.

I'll give you an example:

Thats the table with the codes:

ID          CODE         NAME                                               PARENTID
1           ROOT         IndustryCode                                       NULL
2           IND          Industry                                           1
3           CON          Consulting                                         1
4           FIN          Finance                                            1
5           PHARM        Pharmaceuticals                                    2
6           AUTO         Automotive                                         2
7           STRAT        Strategy                                           3
8           IMPL         Implementation                                     3
9           CFIN         Corporate Finance                                  4
10          CMRKT        Capital Markets                                    9

From which I generate (for displaying in a TreeViewControl) this XML:

<record key="1" parentkey="" Code="ROOT" Name="IndustryCode">
  <record key="2" parentkey="1" Code="IND" Name="Industry">
    <record key="5" parentkey="2" Code="PHARM" Name="Pharmaceuticals" /> 
    <record key="6" parentkey="2" Code="AUTO" Name="Automotive" /> 
  </record>
  <record key="3" parentkey="1" Code="CON" Name="Consulting">
    <record key="7" parentkey="3" Code="STRAT" Name="Strategy" /> 
    <record key="8" parentkey="3" Code="IMPL" Name="Implementation" /> 
  </record>
  <record key="4" parentkey="1" Code="FIN" Name="Finance">
    <record key="9" parentkey="4" Code="CFIN" Name="Corporate Finance">
      <record key="10" parentkey="9" Code="CMRKT" Name="Capital Markets" /> 
    </record>
  </record>
</record>

As you can see, some codes are subordinate to others, for example AUTO << IND << ROOT

What I want (and have absolutely no idea how to realise or even, where to start) is to be able to query another table (where one column is this certain code of course) for a code and get all records with the specific code and all subordinate codes

For example: I query the other table for "IndustryCode = IND[ustry]" and get (of course) the records containing "IND", but also AUTO[motive] and PHARM[aceutical] (= all subordinates)

Its an SQL Express Server 2008 with Advanced Services.

Answer 1

The way I usually do this is by adding a keychain column. For your data:

ID          PARENTID       KEYCHAIN
1           NULL           1
2           1              1.2
3           1              1.3
4           1              1.4
5           2              1.2.5
6           2              1.2.6
7           3              1.3.7
8           3              1.3.8
9           4              1.4.9
10          9              1.4.9.10

This column would obviously have to be calculated at insert using the generated id, but once it's in there you can write your query quite simply.

SELECT *
FROM mytable
WHERE KEYCHAIN like '1.2.%' or KEYCHAIN = '1.2'

There are probably other ways to do this, but I've found this method to work pretty well.

Answer 2

Use:

WITH hierarchy AS (
   SELECT x.code
     FROM TABLE x
    WHERE x.code = @root_code
   UNION ALL
   SELECT y.code
     FROM TABLE y
     JOIN hierarchy h ON h.id = y.parentid)
SELECT z.code
  FROM hierarchy z

It's a typical (now ANSI standard) hierarchical query - there's lots to be found on google about them.