Hackerrank | MySQL | Symmetric Pairs

Symmetric Pairs

Problem

Symmetric Pairs | HackerRank

You are given a table, Functions, containing two columns: X and Y.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

 

-- 13, 13
SELECT X, Y
FROM functions
WHERE X = Y
GROUP BY X, Y
HAVING COUNT(*) = 2

UNION

SELECT f1.X, f1.Y
FROM functions AS f1
    INNER JOIN functions AS f2 ON f1.X = f2.Y AND f1.Y = f2.X
WHERE f1.X < f1.Y
ORDER BY X

SELECT f1.x, f1.y
FROM functions AS f1
    INNER JOIN functions AS f2 ON f1.x=f2.y AND f1.y=f2.x
GROUP BY f1.x, f1.y
HAVING count(*)>=2 or f1.x < f1.y 
ORDER BY f1.x