sqloracle-databaserecursive-query

ORACLE Query for all connected records


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;

Solution

  • 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;