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:
x
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.
Advertisement
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;