sql generating table football's games schedule (variable 2-person teams & all possible games)

Question

Ok, I would like to ask you about something similiar to question: Cross Join without duplicate combinations I have 8 players of table football. We want to play all possible combination games. So for generating all possible teams (2 players) I can use this solution (in my opinion 28 teams are correct):

select distinct
        case when a.id<=b.id then a.id else b.id end as p1,
        case when a.id<=b.id then b.id else a.id end as p2   
from
        scores.players a join scores.players b on a.id!=b.id

but how can I generate all possible games between all possible teams without duplicates? I don't know how compare enought all columns. I tried to use this query and result is 420 combinations, but in my opinion is too much:

select distinct
    t1.p1 as t1p1,
    t1.p2 as t1p2,
    t2.p1 as t2p1,
    t2.p2 as t2p2
from
    (select distinct
        case when a.id<=b.id then a.id else b.id end as p1,
        case when a.id<=b.id then b.id else a.id end as p2   
    from
        scores.players a join scores.players b on a.id!=b.id) t1
     join
            (select distinct
                case when a.id<=b.id then a.id else b.id end as p1,
                case when a.id<=b.id then b.id else a.id end as p2   
            from
                scores.players a
                join scores.players b on a.id!=b.id) t2 on (t1.p1!=t2.p1 and t1.p1!=t2.p2 and t1.p2!=t2.p1 and t1.p2!=t2.p2)

No, I am trying to make my own scoring system for me and my friends ;) sorry, maybe it is as easy as homework, but I don't know how to do it. — lomasz, Apr 18 '15 at 07:10
You have one table, players, with 8 rows (one for each player)? And now you want all possible games? Two teams, two players in each? — jarlh, Apr 18 '15 at 07:38
yes, but the teams are not constant. I changed conditions for join in last line: (t1.p1!=t2.p1 and t1.p1!=t2.p2 and t1.p2!=t2.p1 and t1.p2!=t2.p2) on (t1.p1 — lomasz, Apr 18 '15 at 07:43

jarlh · Accepted Answer · 2015-04-18T08:49:05.587

1

Another try:

select t1.id as t1p1,
       t2.id as t1p2
from players t1
  join players t2 on t1.id > t2.id
  join (select t3.id as t2p1,
               t4.id as t2p2
        from players t3
          join players t4 on t3.id > t4.id)
    on  t2p1 not in (t1.id,t2.id)
    and t2p2 not in (t1.id,t2.id)
    and t1.id > t2p1

Returns 210 rows!

edited Apr 18 '15 at 08:49

answered Apr 18 '15 at 07:42

jarlh

42,561
8
45
63

Thank you, it seems correct, but I am going to check. ;) – lomasz Apr 18 '15 at 08:03
So I checked and I have 70 results, but I think that is not enough. – lomasz Apr 18 '15 at 08:14
Math, that was too many years ago for me... How many games do you expect? – jarlh Apr 18 '15 at 08:26
Yes, It is problem, because I am not pretty sure. I answered my questions, because comments doesn't allow me describe enough. – lomasz Apr 18 '15 at 08:31
Right now it seems to be really OK! Thank you so much! – lomasz Apr 18 '15 at 09:21

score 0 · Answer 2 · answered Apr 18 '15 at 08:29

I checked:

select t1.id as t1p1,
       t2.id as t1p2,
       t3.id as t2p1,
       t4.id as t2p2
from scores.players t1
  join scores.players t2 on t1.id > t2.id
  join scores.players t3 on t2.id > t3.id
  join scores.players t4 on t3.id > t4.id

Result (70 rows):

    t1p1    t1p2    t2p1    t2p2
------------------------------
    d   c   b   a
    e   c   b   a
    e   d   b   a
    e   d   c   a
    e   d   c   b
    f   c   b   a
    f   d   b   a
    f   d   c   a
    f   d   c   b
    f   e   b   a
    f   e   c   a
    f   e   c   b
    f   e   d   a
    f   e   d   b
    f   e   d   c
    g   c   b   a
    g   d   b   a
    g   d   c   a
    g   d   c   b
    g   e   b   a
    g   e   c   a
    g   e   c   b
    g   e   d   a
    g   e   d   b
    g   e   d   c
    g   f   b   a
    g   f   c   a
    g   f   c   b
    g   f   d   a
    g   f   d   b
    g   f   d   c
    g   f   e   a
    g   f   e   b
    g   f   e   c
    g   f   e   d
    h   c   b   a
    h   d   b   a
    h   d   c   a
    h   d   c   b
    h   e   b   a
    h   e   c   a
    h   e   c   b
    h   e   d   a
    h   e   d   b
    h   e   d   c
    h   f   b   a
    h   f   c   a
    h   f   c   b
    h   f   d   a
    h   f   d   b
    h   f   d   c
    h   f   e   a
    h   f   e   b
    h   f   e   c
    h   f   e   d
    h   g   b   a
    h   g   c   a
    h   g   c   b
    h   g   d   a
    h   g   d   b
    h   g   d   c
    h   g   e   a
    h   g   e   b
    h   g   e   c
    h   g   e   d
    h   g   f   a
    h   g   f   b
    h   g   f   c
    h   g   f   d
    h   g   f   e

So i tried to find my first real game which we played:

d e a f

it means: d & e VS a & f

This combination exists in result (f e d a - f & e VS d & a), but it is not exactly the same game

I hope that now I explained my problem more clearly.

Yes, I see the problem. (I'm not supposed to work Saturdays, especially not Saturday mornings... I need more coffee right now!) — jarlh, Apr 18 '15 at 08:34
I believe that is correct number and when I generate using my first way I have 420 combinations and it seems to be doubled, so it is really possible. :) — lomasz, Apr 18 '15 at 08:52

sql generating table football's games schedule (variable 2-person teams & all possible games)

2 Answers2