Sql – Nested join on same table (tree structure)

hierarchyoraclesqltree

My date is organized in tree structure.

The following applies (Oracle SQL syntax):

CREATE TABLE TREE
(
  NAME VARCHAR2(20),
  ID NUMBER(10, 0),
  PARENT NUMBER(10, 0)
)
;

INSERT INTO "TREE" (NAME, ID) VALUES ('a', '1');
INSERT INTO "TREE" (NAME, ID, PARENT) VALUES ('a.1', '2', '1');
INSERT INTO "TREE" (NAME, ID, PARENT) VALUES ('a.2', '3', '1');
INSERT INTO "TREE" (NAME, ID, PARENT) VALUES ('a.2.1', '4', '3');
INSERT INTO "TREE" (NAME, ID, PARENT) VALUES ('a.2.2', '5', '3');
INSERT INTO "TREE" (NAME, ID) VALUES ('b', '6');

I would like to return full tree by id, so for query :

select name, id <<<TODO LOGIC>> where id = 1

I would get

|  name  |  id  |
|  a     |  1   |
|  a.1   |  2   |
|  a.2   |  3   |
|  a.2.1 |  4   |
|  a.2.2 |  5   |

for a sub tree I would get:

select name, id <<<TODO LOGIC>> where id = 3

I would get

|  name  |  id  |
|  a.2   |  3   |
|  a.2.1 |  4   |
|  a.2.2 |  5   |

Where as, for flat entry b, it would get

select name, id <<<TODO LOGIC>> where id = 6

I would get

|  name  |  id  |
|  b     |  6   |

It seems that plain left out join queries fails to fulfill this purpose, or am I missing something?

The following query does return the full structure, but when starting to filter with where statements it fails.

select t1.id t1Id, t2.id t2Id, t1.name t1Name, t2.name t2Name from tree t1 left outer join tree t2 on t1.id = t2.parent

Best Answer

You can use start with - connect by syntax on Oracle. If I'm not mistaken, it goes like this

select * from Tree t
start with t.ID = 1 connect by prior t.ID = t.Parent

But I have no Oracle to check it right away. Maybe its prior t.Parent = t.ID. Beware that it can be slow sometimes, use with caution.

Alternative is to create table to store all indirect relationship between nodes (not just a-a.1, but also a-a.2.1 and so on). You can fill it using PL/SQL recursive stored procedure. Two ways:

Simple way is to make a procedure that will do complete refill of indirect table. You can call it before running reports.
If you need instant effects, you should write refill procedure so that it will update indirect relationship just for one record in tree. Then you prohibit direct inserts and updates to Tree and force them to go via stored PL/SQL procedures (like InsertTree/UpdateTree) which in turn will call procedure to update table with indirect relationships.

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Best Answer

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Related Topic