Query for rows including child rows
- by MAD9
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.
