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)
