Thursday, May 25, 2017

SQL Advanced Join - Symmetric Pairs

Problem

You are given a table, Functions, containing two columns: and Y. Y is the value of some function F at X -- i.e. Y = F(X).

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.
Sample Input

Sample Output
20 20
20 21
22 23
My Answer
SELECT f1.x AS x,
       f1.y AS y
FROM   functions f1,
       functions f2
WHERE  f1.x = f2.y
       AND f1.y = f2.x
       AND f1.x < f1.y
UNION
SELECT x,
       y
FROM   functions
WHERE  x = y
GROUP  BY x,
          y
HAVING Count(x) > 1
ORDER  BY x ASC 

1 comment: