Search code examples
sqloracle-databasecombinationsround-robin

SQL All Possible Round Robin Combinations between two Tables

given table:

create table Person( Name varchar(100) )

where Name is unique for all Persons

What SQL query can generate all possible n!/((n-2)!2!) round robin combinations?

It is assumed that the cardinality of Person is ALWAYS equal to 4

Example Person = {'Anna','Jerome','Patrick','Michael')

Output:

Anna, Jerome
Anna, Patrick
Anna, Michael
Jerome, Patrick
Jerome, Michael
Patrick, Michael

Any help would be appreciated. Thanks!

Here's my answer (I used oracle SQL):

select P1.NAME PERSON1, P2.NAME PERSON2
  from (select rownum RNUM, NAME
        from PERSON) P1,
       (select rownum RNUM, NAME
          from PERSON) P2
 where P1.RNUM < P2.RNUM
Solution 
  • select P1.NAME PERSON1, P2.NAME PERSON2
  from (select rownum RNUM, NAME
        from PERSON) P1,
       (select rownum RNUM, NAME
          from PERSON) P2
 where P1.RNUM < P2.RNUM