CODEWITHSUNDEEP
mysql
Uttkarsh Goel
Uttkarsh Goel

Reputation: 33

Distinct pairs MySQL

I need to find distinct pairs(x,y) from a table consisting 2 columns(X, Y). The numbers which are repeated in the column shouldn't be included in the result set.

Table:

X Y
1 2
2 3
3 4
4 4
5 2
5 6
7 9

Result Set:

X Y
2 3
7 9

5 repeated in X and 2,4 in Y, so they will not form pairs with the corresponding Y and X. The question was asked to me in an interview. Not able to find a solution. Need a query for this. Please help!

Upvotes: 3

Views: 96

Answers (2)

Strawberry
Strawberry

Reputation: 33945

E.g.: 

SELECT DISTINCT a.* 
           FROM my_table a 
           LEFT 
           JOIN my_table b 
             ON b.x = a.x 
            AND b.y <> a.y 
           LEFT 
           JOIN my_table c 
             ON c.y=a.y 
            AND c.x <> a.x 
          WHERE b.x IS NULL 
            AND c.x IS NULL;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

SELECT X, Y
FROM yourTable
WHERE X IN (SELECT X FROM yourTable GROUP BY X HAVING COUNT(*) = 1) AND
      Y IN (SELECT Y FROM yourTable GROUP BY Y HAVING COUNT(*) = 1)

@strawberry appears to have found a way to do this using joins, but the option which popped into my head was to simply use non-correlated subqueries to find the X and Y values which appear only once in each respective column. Then, just use a WHERE clause to check that each X and Y value falls in these sets.

Upvotes: 4

Related Questions