| Subject | Recursive query |
|---|---|
| Author | |
| Post date | 2014-06-05T09:30:50Z |
Hi,
I have a table with the following DDL:
CREATE TABLE LOCALITY (
ID INTEGER NOT NULL,
PARENT_ID INTEGER,
CT_NODETYPES_ID INTEGER,
DESCRIPTION VARCHAR(30),
);
The foreign key: CT_NODETYPES_ID refers to a table CT_NODETYPES with the following potential values:
| ID | DESCRIPTION |
| 1 | Country |
| 2 | Province |
| 3 | Farm registration districts |
| 4 | District Municipality |
| 5 | Metropolitan Municipality |
| 6 | Local Municipality |
Typical data for LOCALITY are:
| ID | PARENT_ID | CT_NODETYPES_ID | DESCRIPTION |
| 1 | 1 | South Africa | |
| 2 | 1 | 2 | CONTINENTAL SHELF SOUTH AFRICA |
| 3 | 1 | 2 | EASTERN CAPE |
| 4 | 1 | 2 | FREE STATE |
| 5 | 1 | 2 | GAUTENG |
| 6 | 1 | 2 | KWAZULU-NATAL |
| 7 | 1 | 2 | LIMPOPO |
| 8 | 1 | 2 | MPUMALANGA |
| 9 | 1 | 2 | NORTH WEST |
| 10 | 1 | 2 | NORTHERN CAPE |
| 11 | 1 | 2 | WESTERN CAPE |
| 128 | 10 | 3 | BARKLY WEST |
| 129 | 10 | 3 | BRITSTOWN |
| 130 | 10 | 3 | CALVINIA |
| 146 | 11 | 3 | BEAUFORT WEST |
| 147 | 11 | 3 | BELLVILLE |
| 148 | 11 | 3 | BREDASDORP |
| 178 | 9 | 4 | Bojanala |
| 179 | 7 | 4 | Capricorn |
| 180 | 9 | 4 | Central |
| 181 | 9 | 4 | Bophirima |
| 182 | 5 | 5 | Ekurhuleni |
| 183 | 5 | 5 | Johannesburg |
| 184 | 5 | 6 | Mogale |
| 185 | 7 | 4 | Mopani |
| 186 | 7 | 4 | Sekhukhune |
| 187 | 7 | 4 | Vhembe |
| 188 | 7 | 4 | Waterberg |
| 189 | 9 | 4 | Southern |
| 190 | 5 | 5 | Tshwane |
I then typically use (as a foreign key) a record from LOCALITY at the lowest level, e.g. ID = 148 as the locality (farm registration district) of an abandoned mine or ID = 184 as the locality (Local municipality) of a unsafe mine opening.
When I display the data in a grid of a web application or in a column of a report, I also want to display the province (ID = 2 in table CT_NODETYPES)
I also want to generate stats from the mine table, e.g.
Select Province_Name, Mine_Name, Count(ID) as Total from V_MINES
where Province_ID = 4
Group by Province_Name, Mine_Name
V_MINES is a view spanning several tables.
The problem I have is that (in the MINES table) I do not store the ID of the provinces but I store the ID of the farm registration districts (a child node of the province node).
In the table for unsafe mine openings, I store the ID of local, metropolitan or district municipality.
My use, and knowledge, of recursive common table expressions (CTE) is limited to iterate downwards from a node in a tree-structure (used as foreign key in a table).
Now what I'm actually want is to look up the parent at a certain level (or classified as a certain type) of a "lower" record (node) in a tree-structure.
Surely there must be a way to solve this problem?
Regards,
Nols Smit
(Council for Geoscience, South Africa)