Find records which cross-reference each other

I want to extract all the rows from a database table, where the rows cross-reference each other.

My table contains 2 rows: ref1 & ref2

Table example:

ID  ref1  ref2
01    23    83
02    77    55
03    83    23
04    13    45

In this case, I want my query to return only rows 01 and 03, because they cross-reference each other.

Is this possible using a single query, or will I need to iterate the entire table manually?

I’m using MySQL.

Answer

A simple JOIN can do that in a straight forward manner;

SELECT DISTINCT a.*
FROM mytable a
JOIN mytable b
  ON a.ref1 = b.ref2 AND a.ref2 = b.ref1;

An SQLfiddle to test with.