Query for rows including child rows
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.
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.
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.
精彩评论