Find records which cross-reference each other

Question

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 1

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.

Answer 2

select
    *
from
    tbl t1
where
    exists (
        select
            'x'
        from
            tbl t2
        where
            t1.ref1 = t2.ref2 and
            t1.ref2 = t2.ref1
    )