CODEWITHSUNDEEP
sqloracle-database
user1698923
user1698923

Reputation: 25

Oracle Recursive Join - Many to Many Relationship

I've been trying for a few hours without success, to recursively join a table to itself. It's probably simplest to see the problem in this SQL Fiddle, rather than me trying to explain the linking relationship:

http://sqlfiddle.com/#!4/367c3/14

In the above example, the actual data might nest up to 5 layers deep, perhaps more. I'd like to write a query that handles joins to any number of layers.

From doing some searches, it seems that it's possible to recursively join the data with CONNECT BY PRIOR, and other methods. I just haven't been able to get it to work :(. It'd be awesome if one of you guru's could show me how it's done.

Upvotes: 1

Views: 810

Answers (1)

michaelgulak
michaelgulak

Reputation: 631

Copying your tables from SQL Fiddle for others to see in case the external link becomes invalid in the future:

* DEVICE TABLE
DEVICEID DEVICENAME
-------------------
1        Device1
2        Device2
3        Device3
4        Device4
5        Device5
6        Device6
7        Device7
8        Device8
9        Device9
10       Device10

* CONNECTION TABLE
IDPARENT IDCHILD
----------------
1        2
3        4
4        5
6        7
7        8
4        8
4        8
5        9

I don't know what data exactly you want to query from the tables, but if you are just concerned with the self-"join" using a connect by prior, here you go:

select distinct d.deviceid, d.devicename, connect_by_root(deviceid) rootdeviceid
from device d
    left join connection c on (d.deviceid = c.idchild)
connect by prior d.deviceid = c.idparent
start with c.idparent is null
order by d.deviceid

Note that there are 11 rows returned by this query given the table you provided -- this is because device 8 actually has two root nodes in the hierarchy you provided, 3 and 6.

Upvotes: 1

Related Questions