Search code examples
mysqlsqlcombinationsrestrict

SQL restricted set of combinations

I have a table with columns A,B,C and I'd like to get all combinations of records having {B,C} unique. That is both B value and C value will appear only once in one set.

Do you have any ideas how to achieve that? I assume the output has to contain one combination on a single row, which is not a problem.

To make it clear here is an example:

  • 1,1,0
  • 6,1,1
  • 1,1,2
  • 3,2,0
  • 5,2,1
  • 1,2,3

One possible combination is {1,1,0},{1,2,3}, while {6,1,1},{5,2,1} isn't, because the C column value '1' is not unique. What I'd like to get is such an output:

1,1,0,1,2,3

6,1,1,1,2,3

IOW the output will be n-tuples having B,C values unique.

Solution

  • I think you want a strange verson of a self join:

    select t1.*, t2.*
from table t1 join
     table t2
     on t1.b <> t2.b and t1.c <> t2.c;

    This will return all pairs from the table where the b columns have distinct values and the c columns have distinct values.