finding bi-directional connections in a table

Question

I have the following SQL result set (as result of a join FWIW):

A,C
B,E
C,A
D,A

Every value represents a node. So the total set of nodes involved is A,B,C,D,E. Every row describes a directed edge.

A -> C
B -> E
C -> A
D -> A

Of course this can be simplified to

A <-> C
B -> E
D -> A

Now I would like to filter out the rows that do not have a bi-directional counterpart. So the final result set should be

A,C
C,A

or

A,C

in this case.

I am looking the best way to express this in SQL.

Answer 1

Imagine a table Nodes with columns Node1 and Node2.

SELECT node1, node2
FROM nodes n
WHERE EXISTS (SELECT 1 FROM nodes WHERE node1 = n.node2 AND node2 = n.node1)

Of course you'll want to make sure its appropriate indexed, namely:

(node1, node2)

and

(node2, node1)

This will still get you A,C and C,A. To filter those out change the query to:

SELECT node1, node2
FROM nodes n
WHERE EXISTS (SELECT 1 FROM nodes WHERE node1 = n.node2 AND node2 = n.node1)
AND node1 < node2

assuming there is a natural ordering you can take advantage of.

Answer 2

-- Temp table creating to insert example rows
SELECT 
'A' AS Col1, 'C' AS Col2
INTO #TEMP
UNION
SELECT 'B', 'E'
UNION
SELECT 'C', 'A'
UNION
SELECT 'D', 'A'


SELECT A.* FROM #TEMP A, #TEMP B
WHERE (A.COL1 = B.COL2 AND A.COL2 = B.COL1)

Answer 3

Inner-join the node pairs to themself?

select node1, node2
from nodes n
inner join nodes n2
on n.node1 = n2.node2 and n.node2 = n2.node1

(replace 'nodes' with your subquery)