1

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!

Improve this question
4
  • 2
    I would strongly recommend you use a single table for a hierarchy. It's more flexible and can support unlimited number of levels. Commented Feb 6, 2023 at 21:18
  • 1
    Typically a hierarchy like that is handled in a single table, with an ID column and a PARENT_ID column to point to the level above. You can then use the connect by clause to have Oracle automatically recurse the relationship tree. You're not limited physically to a set number of levels that way, or using complicated views to build a coherent view of your data. Commented Feb 6, 2023 at 21:20
  • Thank you for your comment @TheImpaler - I modified the question to more accurately represent my tables, where each table represents a different object (ex. State, City, ZipCode) with their own unique properties/IDs, and I only need 3 levels for the hierarchy in this case. Would you still recommend the single table approach in that case? Commented Feb 6, 2023 at 22:07
  • and @pmdba - thank you for your comment as well, I added some more context in the comment above and in my post, would you still recommend the single table approach in that case? Commented Feb 6, 2023 at 22:07

1 Answer 1

Reset to default
2

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

fiddle

Update

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for the thorough answer - I modified the question to be a little more clear, like if each table (ex. State, City, ZipCode) had their own unique properties and different/unique IDs - would you still recommend this approach for something like that?
@srepj Updated - if the tables are distinct data sets then, no, just use 3 tables and JOIN them.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.