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.
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments