I have a joining table that performs a two-way binding using a single record. In the example below, the record with id=8 is linked to the record with id=4, and the record with id=4 is linked to the record with id=8.
id | object_id | connected_object_id |
---|---|---|
1 | 1 | 2 |
2 | 1 | 4 |
3 | 2 | 4 |
4 | 5 | 1 |
5 | 8 | 4 |
6 | 12 | 2 |
I want to query for all objects directly or indirectly connected to the given object.
My query looks like:
Select * from TABLE t
START WITH t.object_id = xxx
CONNECT BY NOCYCLE PRIOR t.object_id = t.object_id
OR PRIOR t.connected_object_id = t.connected_object_id
OR PRIOR t.object_id = t.connected_object_id
OR PRIOR t.connected_object_id = t.object_id
For example, if I query for the object with id=12, I would like to get all the records from the above table.
Unfortunately, this query will not work if the records with id = 12 are only in the connected_object_id column. Additionally, the query hangs in some cases.
@Final query with @Abdul Alim Shakir help
WITH all_links(source, target) AS (
SELECT object_id, connected_object_id FROM connections
UNION
SELECT connected_object_id, object_id FROM connections
),
connected(object_id) AS (
SELECT 12 FROM DUAL
UNION ALL
SELECT source from all_links START WITH source = 12
CONNECT BY NOCYCLE PRIOR source = target
)
SELECT DISTINCT c.*
FROM connections c
JOIN connected co
ON c.object_id = co.object_id OR c.connected_object_id =
co.object_id;
Assuming that your joining table name is connections
. It is required to find all records in the connections
table that are directly or indirectly connected to a given object. First of all, we have to make this table bidirectional so the connection can work in both ways. all_links
virtual table serves this purpose. Another virtual table connected
is working recursively to discover every object linked in any way to given object. Last of all, we have to select to get all the records from the above table.
WITH RECURSIVE all_links(source, target) AS (
SELECT object_id, connected_object_id FROM connections
UNION
SELECT connected_object_id, object_id FROM connections
),
connected(object_id) AS (
SELECT 12
UNION
SELECT a.target
FROM all_links a
JOIN connected c ON a.source = c.object_id
)
SELECT DISTINCT c.*
FROM connections c
JOIN connected co
ON c.object_id = co.object_id OR c.connected_object_id = co.object_id;