Find records that cross-reference each other

Question

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

My table contains 2 rows: ref1andref2

Example table:

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 manually iterate over the entire table?

I am using MySQL.

Answer 1

A simple JOIN can do this in a direct way;

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

SQLfiddle for testing with .

Answer 2

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