Tenho uma tabela de junção que realiza uma associação bidirecional usando um único registro. No exemplo abaixo, o registro com id=8 está vinculado ao registro com id=4, e o registro com id=4 está vinculado ao registro com id=8.
| eu ia | ID do objeto | id_do_objeto_conectado |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 4 |
| 3 | 2 | 4 |
| 4 | 5 | 1 |
| 5 | 8 | 4 |
| 6 | 12 | 2 |
Quero consultar todos os objetos conectados direta ou indiretamente ao objeto fornecido.
Minha consulta se parece com:
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
Por exemplo, se eu consultar o objeto com id=12, gostaria de obter todos os registros da tabela acima.
Infelizmente, esta consulta não funcionará se os registros com id = 12 estiverem apenas na coluna connected_object_id. Além disso, a consulta trava em alguns casos.
@Consulta final com ajuda de @Abdul Alim Shakir
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;