I'm researching ways to create a hierarchical SQL query for my Oracle database where Table1 and Table2 have a many-to-one relationship, and Table2 and Table3 have a many-to-one relationship and where Table1 is the parent of Table2, and Table2 is the parent of Table3.
I'm trying to create a query where Table1 represents LEVEL 1, Table2 represents LEVEL2, and Table3 represents LEVEL 3 in Oracle's LEVEL pseudocolumn.
A simplified table structure is as follows, where TABLE1, TABLE2, and TABLE3 each have columns that are unique to them and their own unique IDs and descriptions.
For example, imagine Table1 represent a State, Table2 represents a City, and Table3 represents a Zip code - these all have their own unique properties, and a State has many Cities, and a City has many Zip Codes.
TABLE1(id (PK), t1name, description)
TABLE2(id (PK), t2name, table1ID (FK), description, var1, var2)
TABLE3(id (PK), t3name, table2ID (FK), description, var3, var4)
I've tried using a query like this but the LEVEL pseudocolumn is always '1' and the CONNECT_BY_ISLEAF pseudocolumn is also '1':
WITH alltabs as
(Select 'T1' as src, table1.ID, NULL AS parent1Id, NULL as parent2Id, table1.name as name
from table1
union
Select 'T2' as src, table2.ID, table2.table1Id, NULL as parent2Id, table2.name as name
from table2
union
Select 3 as src, table3.ID, NULL AS parent1Id, table3.table2id, table3.name as name
from table3)
Select LEVEL, src, parent1Id, parent2Id, name, CONNECT_BY_ISLEAF
from alltabs
connect by (id = parent1Id and (src = 'T1' or src = 'T2'))
or (id = parent2Id AND (src = 'T2' or src = 'T3'))
The result I'm looking for is one where the LEVEL pseudocolumn is '1' for Table1, '2' for Table2, and '3' for Table3.
Any guidance would be appreciated - I'm also open to changing the table structure. Thank you!
Just use a single table with a self-referential foreign key:
CREATE TABLE table1 (
id NUMBER
CONSTRAINT table1__id__pk PRIMARY KEY,
name VARCHAR2(50),
parent CONSTRAINT table1__parent__fk REFERENCES table1 (id)
);
Then you can use:
SELECT t.*,
LEVEL,
CONNECT_BY_ISLEAF,
SYS_CONNECT_BY_PATH(name, ' > ') AS ancestors
FROM table1 t
START WITH parent IS NULL
CONNECT BY PRIOR id = parent
ORDER SIBLINGS BY name;
Which, for the sample data:
INSERT INTO table1 (id, name, parent)
SELECT 1, 'Alice', NULL FROM DUAL UNION ALL
SELECT 2, 'Beryl', 1 FROM DUAL UNION ALL
SELECT 3, 'Carol', 1 FROM DUAL UNION ALL
SELECT 4, 'Debra', 2 FROM DUAL UNION ALL
SELECT 5, 'Emily', 3 FROM DUAL UNION ALL
SELECT 6, 'Fiona', 4 FROM DUAL;
Outputs:
| ID | NAME | PARENT | LEVEL | CONNECT_BY_ISLEAF | ANCESTORS |
|---|---|---|---|---|---|
| 1 | Alice | null | 1 | 0 | > Alice |
| 2 | Beryl | 1 | 2 | 0 | > Alice > Beryl |
| 4 | Debra | 2 | 3 | 0 | > Alice > Beryl > Debra |
| 6 | Fiona | 4 | 4 | 1 | > Alice > Beryl > Debra > Fiona |
| 3 | Carol | 1 | 2 | 0 | > Alice > Carol |
| 5 | Emily | 3 | 3 | 1 | > Alice > Carol > Emily |
Imagine Table1 represent a State, Table2 represents a City, and Table3 represents a Zip code - these all have their own unique properties, and a State has many Cities, and a City has many Zip Codes.
If you have three distinct sets of data (states, cities and zip codes) each with properties that are unique to that set of data then use three different three tables.
CREATE TABLE state (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
government VARCHAR2(50),
flag BLOB
);
CREATE TABLE city (
id NUMBER PRIMARY KEY,
state_id REFERENCES state (id),
mayor VARCHAR2(20),
rat_population NUMBER(4,0)
);
CREATE TABLE zip_code (
id NUMBER PRIMARY KEY,
state_id REFERENCES state (id),
nearest_city_id REFERENCES city (id),
refuse_collection_day VARCHAR2(9)
);
If you want to find information about the tables then just use a JOIN on the referential constraints when you want to combine two-or-more data sets. You do not want to use a hierarchical query for this type of data.