Skip to Main Content
  • Questions

  • Procedure to create sporting fixture failing to respect condition query against the same table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Andrew.

Asked: February 01, 2018 - 11:22 pm UTC

Last updated: February 23, 2022 - 2:31 pm UTC

Version: 12g

Viewed 1000+ times

You Asked

Hi TOM,

I'm trying to generate a sporting fixture from a list of all possible matchups (ALL_POSSIBLE_FIXTURES - which contains each team playing at home against its opponent, and the reverse of that - so AAA (home) v BBB (away) and BBB (home) v AAA (away) are distinct and valid potential matches), with a few conditions. There are 18 teams, playing each other once over 17 rounds with 9 games in each round. Teams can't appear twice in the same round, nor can they play more than 9 games either home or away over the 17 rounds. And ideally, they can't play home or away twice in a row.

The way I have tried to solve this is by creating a procedure to loop through the number of rounds and matches, inserting matches that align with the defined conditions into that table, and then using that table to then define the future appropriate insertions.

For example, statement 3 in the LiveSQL contains the following:

FOR ROUND_NO IN 1 .. 17 LOOP   
    FOR MATCH_ID IN 1 .. 9 LOOP   
      INSERT INTO FIXTURE(HOME_TEAM, AWAY_TEAM, ROUND)   
   
      SELECT HOME_TEAM, AWAY_TEAM, ROUND_NO AS ROUND FROM (   
        SELECT HOME_TEAM, AWAY_TEAM, DBMS_RANDOM.VALUE AS RND FROM ALL_POSSIBLE_FIXTURES   
        WHERE   
          -- this match cannot be in this round   
          (HOME_TEAM||AWAY_TEAM NOT IN (SELECT HOME_TEAM||AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM||HOME_TEAM NOT IN (SELECT AWAY_TEAM||HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          -- the teams cannot already be playing each other in this configuration   
          (HOME_TEAM||AWAY_TEAM NOT IN (SELECT HOME_TEAM||AWAY_TEAM FROM FIXTURE)) AND   
          (AWAY_TEAM||HOME_TEAM NOT IN (SELECT AWAY_TEAM||HOME_TEAM FROM FIXTURE)) AND   
          -- the teams cannot have had two home games in the last two rounds (***** NB. This doesnt appear to be working *****)   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND > ROUND_NO-2 GROUP BY HOME_TEAM HAVING COUNT(*) > 1)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND > ROUND_NO-2 GROUP BY AWAY_TEAM HAVING COUNT(*) > 1)) AND   
          -- these teams cannot be scheduled to play more than 11 games home or away   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE GROUP BY HOME_TEAM HAVING COUNT(*) > 9)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE GROUP BY AWAY_TEAM HAVING COUNT(*) > 9)) AND   
          -- these teams cannot already be in this round, either home or away   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (HOME_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO))   
          ORDER BY 3   
      ) WHERE ROWNUM = 1;   
      COMMIT;   
    END LOOP;   
  END LOOP;


But while it works fairly well, I am having trouble getting it to respect the home/away sequence condition -
 (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND > ROUND_NO-2 GROUP BY HOME_TEAM HAVING COUNT(*) > 1))


As can be seen from the sample output (which is random and will change when run again) where team AAA is the home team in three successive rounds in rounds 2-4.

I would have thought that at least by round 4, this home team would appear as having a count > 1 in the last two rounds, and therefore not be a valid selection for the home team in a round 4 match. But as you can see, that's not the case.

Can someone explain to me what is going on here?

Thanks a lot,
Andrew

EDIT: On request - Further information about ALL_POSSIBLE_FIXTURES table

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/faqq062le6diptv2g710ae0he

and Connor said...

I did this row by row rather than insert-select, so we could output each candidate row as we went.

Here are my edits

SQL> CREATE TABLE ALL_POSSIBLE_FIXTURES AS
  2  WITH X(TEAM) AS (
  3  SELECT 'AAA' FROM DUAL UNION ALL
  4  SELECT 'BBB' FROM DUAL UNION ALL
  5  SELECT 'CCC' FROM DUAL UNION ALL
  6  SELECT 'DDD' FROM DUAL UNION ALL
  7  SELECT 'EEE' FROM DUAL UNION ALL
  8  SELECT 'FFF' FROM DUAL UNION ALL
  9  SELECT 'GGG' FROM DUAL UNION ALL
 10  SELECT 'HHH' FROM DUAL UNION ALL
 11  SELECT 'III' FROM DUAL UNION ALL
 12  SELECT 'JJJ' FROM DUAL UNION ALL
 13  SELECT 'KKK' FROM DUAL UNION ALL
 14  SELECT 'LLL' FROM DUAL UNION ALL
 15  SELECT 'MMM' FROM DUAL UNION ALL
 16  SELECT 'NNN' FROM DUAL UNION ALL
 17  SELECT 'OOO' FROM DUAL UNION ALL
 18  SELECT 'PPP' FROM DUAL UNION ALL
 19  SELECT 'QQQ' FROM DUAL UNION ALL
 20  SELECT 'RRR' FROM DUAL
 21  )
 22  SELECT
 23    X1.TEAM AS HOME_TEAM,
 24    X2.TEAM AS AWAY_TEAM
 25  FROM X X1
 26  CROSS JOIN X X2
 27  WHERE X1.TEAM <> X2.TEAM ;

Table created.

SQL>
SQL> CREATE TABLE FIXTURE
  2  ( "HOME_TEAM" VARCHAR2(15 BYTE),
  3    "AWAY_TEAM" VARCHAR2(15 BYTE),
  4    "ROUND" NUMBER,
  5    MATCH number
  6  );

Table created.

SQL> set serverout on
SQL> DECLARE
  2    v_cnt NUMBER;
  3
  4  PROCEDURE insertGames IS
  5    l_home varchar2(10);
  6    l_away varchar2(10);
  7    l_round int;
  8    l_match int;
  9  BEGIN
 10    delete FIXTURE;
 11    FOR iter_round IN 1 .. 17 LOOP
 12      FOR iter_match IN 1 .. 9 LOOP
 13        SELECT HOME_TEAM, AWAY_TEAM, iter_round , iter_match
 14        into l_home, l_away, l_round, l_match
 15        FROM (
 16          select home_team, away_team, dbms_random.value as rnd from all_possible_fixtures a
 17          --
 18          -- the teams cannot already be playing each other in this configuration
 19          --
 20          where  not exists ( select null from fixture where home_team||away_team = a.home_team||a.away_team )
 21            and  not exists ( select null from fixture where away_team||home_team = a.away_team||a.home_team )
 22          --
 23          -- these teams cannot be scheduled to play more than 9 games home or away
 24          --
 25            and  ( select count(*) from fixture where home_team = a.home_team ) <= 8
 26            and  ( select count(*) from fixture where away_team = a.away_team ) <= 8
 27          --
 28          -- the teams cannot have had two home games in the last two rounds (***** NB. This doesnt appear to be working *****)
 29          --
 30            and  ( select count(*) from fixture where round > iter_round-2 and home_team = a.home_team ) < 2
 31          --
 32          -- these teams cannot already be in this round, either home or away
 33          --
 34            and not exists ( select null from fixture where round = iter_round and home_team = a.home_team)
 35            and not exists ( select null from fixture where round = iter_round and home_team = a.away_team)
 36            and not exists ( select null from fixture where round = iter_round and away_team = a.home_team)
 37            and not exists ( select null from fixture where round = iter_round and away_team = a.away_team)
 38          order by 3
 39        ) WHERE ROWNUM = 1;
 40
 41        dbms_output.put_line('Round: '||lpad(l_round,2)||'  Match:'||lpad(l_match,2)||'   '||l_home||','||l_away);
 42        INSERT INTO FIXTURE(HOME_TEAM, AWAY_TEAM, ROUND, MATCH)
 43        values (l_home, l_away, l_round,l_match );
 44
 45        COMMIT;
 46      END LOOP;
 47    END LOOP;
 48  END;
 49
 50  BEGIN
 51    for i in 1 .. 100
 52    loop
 53      begin
 54        insertGames;
 55        exit;
 56      exception
 57        when no_data_found then
 58          if i = 100 then raise; end if;
 59      end;
 60    end loop;
 61  END;
 62  /

[snip]

Round:  1  Match: 1   FFF,DDD
Round:  1  Match: 2   HHH,QQQ
Round:  1  Match: 3   III,CCC
Round:  1  Match: 4   BBB,PPP
Round:  1  Match: 5   JJJ,NNN
Round:  1  Match: 6   KKK,AAA
Round:  1  Match: 7   MMM,EEE
Round:  1  Match: 8   LLL,RRR
Round:  1  Match: 9   OOO,GGG
Round:  2  Match: 1   FFF,BBB
Round:  2  Match: 2   GGG,EEE
Round:  2  Match: 3   JJJ,OOO
Round:  2  Match: 4   DDD,MMM
Round:  2  Match: 5   HHH,III
Round:  2  Match: 6   RRR,QQQ
Round:  2  Match: 7   PPP,NNN
Round:  2  Match: 8   LLL,CCC
Round:  2  Match: 9   AAA,KKK
Round:  3  Match: 1   KKK,DDD
Round:  3  Match: 2   HHH,EEE
Round:  3  Match: 3   QQQ,PPP
Round:  3  Match: 4   OOO,FFF
Round:  3  Match: 5   RRR,III
Round:  3  Match: 6   BBB,CCC
Round:  3  Match: 7   GGG,AAA
Round:  3  Match: 8   NNN,MMM
Round:  3  Match: 9   LLL,JJJ
Round:  4  Match: 1   RRR,PPP
Round:  4  Match: 2   KKK,BBB
Round:  4  Match: 3   FFF,NNN
Round:  4  Match: 4   III,AAA
Round:  4  Match: 5   EEE,GGG
Round:  4  Match: 6   OOO,CCC
Round:  4  Match: 7   HHH,MMM
Round:  4  Match: 8   LLL,DDD
Round:  4  Match: 9   QQQ,JJJ
Round:  5  Match: 1   HHH,PPP
Round:  5  Match: 2   EEE,BBB
Round:  5  Match: 3   III,NNN
Round:  5  Match: 4   LLL,KKK
Round:  5  Match: 5   QQQ,AAA
Round:  5  Match: 6   CCC,MMM
Round:  5  Match: 7   GGG,FFF
Round:  5  Match: 8   OOO,DDD
Round:  5  Match: 9   JJJ,RRR
Round:  6  Match: 1   QQQ,GGG
Round:  6  Match: 2   CCC,KKK
Round:  6  Match: 3   OOO,RRR
Round:  6  Match: 4   JJJ,LLL
Round:  6  Match: 5   PPP,BBB
Round:  6  Match: 6   DDD,EEE
Round:  6  Match: 7   NNN,HHH
Round:  6  Match: 8   AAA,III
Round:  6  Match: 9   FFF,MMM
Round:  7  Match: 1   RRR,FFF
Round:  7  Match: 2   KKK,GGG
Round:  7  Match: 3   JJJ,HHH
Round:  7  Match: 4   MMM,PPP
Round:  7  Match: 5   NNN,III
Round:  7  Match: 6   BBB,DDD
Round:  7  Match: 7   CCC,OOO
Round:  7  Match: 8   EEE,QQQ
Round:  7  Match: 9   LLL,AAA
Round:  8  Match: 1   OOO,LLL
Round:  8  Match: 2   PPP,RRR
Round:  8  Match: 3   KKK,FFF
Round:  8  Match: 4   BBB,JJJ
Round:  8  Match: 5   GGG,HHH
Round:  8  Match: 6   EEE,NNN
Round:  8  Match: 7   MMM,QQQ
Round:  8  Match: 8   CCC,AAA
Round:  8  Match: 9   III,DDD
Round:  9  Match: 1   HHH,RRR
Round:  9  Match: 2   III,BBB
Round:  9  Match: 3   GGG,QQQ
Round:  9  Match: 4   EEE,MMM
Round:  9  Match: 5   CCC,PPP
Round:  9  Match: 6   FFF,JJJ
Round:  9  Match: 7   DDD,NNN
Round:  9  Match: 8   KKK,LLL
Round:  9  Match: 9   AAA,OOO
Round: 10  Match: 1   AAA,CCC
Round: 10  Match: 2   EEE,FFF
Round: 10  Match: 3   JJJ,MMM
Round: 10  Match: 4   QQQ,BBB
Round: 10  Match: 5   PPP,HHH
Round: 10  Match: 6   NNN,KKK
Round: 10  Match: 7   GGG,LLL
Round: 10  Match: 8   III,OOO
Round: 10  Match: 9   RRR,DDD
Round: 11  Match: 1   HHH,NNN
Round: 11  Match: 2   III,JJJ
Round: 11  Match: 3   KKK,MMM
Round: 11  Match: 4   BBB,OOO
Round: 11  Match: 5   RRR,LLL
Round: 11  Match: 6   GGG,DDD
Round: 11  Match: 7   QQQ,CCC
Round: 11  Match: 8   FFF,AAA
Round: 11  Match: 9   EEE,PPP
Round: 12  Match: 1   BBB,NNN
Round: 12  Match: 2   MMM,GGG
Round: 12  Match: 3   KKK,JJJ
Round: 12  Match: 4   FFF,CCC
Round: 12  Match: 5   AAA,DDD
Round: 12  Match: 6   III,EEE
Round: 12  Match: 7   LLL,PPP
Round: 12  Match: 8   HHH,OOO
Round: 12  Match: 9   QQQ,RRR
Round: 13  Match: 1   MMM,KKK
Round: 13  Match: 2   FFF,RRR
Round: 13  Match: 3   BBB,GGG
Round: 13  Match: 4   QQQ,EEE
Round: 13  Match: 5   CCC,HHH
Round: 13  Match: 6   AAA,PPP
Round: 13  Match: 7   OOO,III
Round: 13  Match: 8   DDD,JJJ
Round: 13  Match: 9   NNN,LLL
Round: 14  Match: 1   KKK,OOO
Round: 14  Match: 2   PPP,GGG
Round: 14  Match: 3   JJJ,FFF
Round: 14  Match: 4   BBB,HHH
Round: 14  Match: 5   LLL,NNN
Round: 14  Match: 6   DDD,AAA
Round: 14  Match: 7   MMM,RRR
Round: 14  Match: 8   CCC,EEE
Round: 14  Match: 9   QQQ,III
Round: 15  Match: 1   PPP,FFF
Round: 15  Match: 2   BBB,AAA
Round: 15  Match: 3   OOO,QQQ
Round: 15  Match: 4   JJJ,CCC
Round: 15  Match: 5   MMM,LLL
Round: 15  Match: 6   EEE,RRR
Round: 15  Match: 7   NNN,GGG
Round: 15  Match: 8   III,HHH
Round: 15  Match: 9   DDD,KKK
Round: 16  Match: 1   RRR,HHH
Round: 16  Match: 2   JJJ,KKK
Round: 16  Match: 3   EEE,LLL
Round: 16  Match: 4   DDD,GGG
Round: 16  Match: 5   NNN,OOO
Round: 16  Match: 6   MMM,CCC
Round: 16  Match: 7   AAA,BBB
Round: 16  Match: 8   PPP,III
Round: 16  Match: 9   FFF,QQQ
Round: 17  Match: 1   MMM,HHH
Round: 17  Match: 2   LLL,EEE
Round: 17  Match: 3   CCC,JJJ
Round: 17  Match: 4   GGG,III
Round: 17  Match: 5   PPP,KKK
Round: 17  Match: 6   DDD,BBB
Round: 17  Match: 7   NNN,QQQ
Round: 17  Match: 8   AAA,FFF
Round: 17  Match: 9   RRR,OOO

PL/SQL procedure successfully completed.

SQL>
SQL> select home_team||away_team
  2  from fixture
  3  group by home_team||away_team
  4  having count(*) > 1;

no rows selected

SQL>
SQL> select round, team
  2  from (
  3  select round, home_team team
  4  from fixture
  5  union all
  6  select round, away_team
  7  from fixture
  8  )
  9  group by round, team
 10  having count(*) > 1;

no rows selected

SQL>
SQL> select home_team, count(*)
  2  from fixture
  3  group by home_team
  4  order by 2;

HOME_TEAM         COUNT(*)
--------------- ----------
GGG                      8
CCC                      8
PPP                      8
AAA                      8
DDD                      8
OOO                      8
NNN                      8
HHH                      8
RRR                      8
QQQ                      9
JJJ                      9
BBB                      9
FFF                      9
MMM                      9
III                      9
KKK                      9
LLL                      9
EEE                      9

18 rows selected.

SQL>
SQL> select away_team, count(*)
  2  from fixture
  3  group by away_team
  4  order by 2;

AWAY_TEAM         COUNT(*)
--------------- ----------
MMM                      8
BBB                      8
JJJ                      8
KKK                      8
LLL                      8
QQQ                      8
FFF                      8
III                      8
EEE                      8
PPP                      9
OOO                      9
CCC                      9
NNN                      9
GGG                      9
AAA                      9
HHH                      9
RRR                      9
DDD                      9

18 rows selected.

SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Andrew, February 21, 2022 - 12:27 am UTC

Thanks Connor, but unfortunately that doesn't solve the main issue I was having.

For example, in your output the team DDD play each game from round 13 at home.

Round: 13 Match: 8 DDD,JJJ
Round: 14 Match: 6 DDD,AAA
Round: 15 Match: 9 DDD,KKK
Round: 16 Match: 4 DDD,GGG
Round: 17 Match: 6 DDD,BBB

Why is the query not respecting this requirement?

Andrew, February 23, 2022 - 12:53 am UTC

Managed to get this working by restructuring it slightly. Given the consecutive games requirement wasn't working, I moved that part into a view that would provide what the next round should be (home or away) for that team based on their previous matches.

I originally had it set for no more than two, but it was causing buffer overflows, so I've softened that to no more than three.

create or replace view NEXT_ROUND_VW AS
with x as (
    select 
        a.club_id
      , a.round
        -- effectively the most recent round, which will be the previous one when it's being checked in the creation loop
      , a.home_away
        -- the previous round to the previous round
      , lag(home_Away) over (partition by club_id order by round) round_minus_one
        -- the round before the previous round
      , lag(home_Away, 2) over (partition by club_id order by round) round_minus_two

    from (
        select 
            club_id
          , round
          , case when club.club_id = fix.home_team then 'H' else 'A' end as home_away

        from 
            club_details club
        inner join 
            fixture fix 
                on 
                    club.club_id = fix.home_team or 
                    club.club_id = fix.away_team
    ) a
)

select 
    club_id
  , round
  , home_Away
  , round_minus_one
  , round_minus_two
  , case 
        -- if we've had three of the same in a row, then the next one must be the opposite
        when home_Away = 'H' and round_minus_one = 'H' and round_minus_two = 'H' THEN 'A'
        when home_away = 'A' and round_minus_one = 'A' and round_minus_two = 'A' THEN 'H'
        ELSE NULL
    end as NEXT_ROUND
from x


Then in the loop, we join to this view and it works pretty well

set serverout on
DECLARE
  v_cnt NUMBER;
  v_home_game_limit NUMBER := 9;
  v_total_rounds NUMBER := 17;
  v_year NUMBER := 2021;
  

PROCEDURE insertGames IS
  l_home varchar2(10);
  l_away varchar2(10);
  l_round int;
  l_match int;
BEGIN
  delete from FIXTURE where YEAR = v_year;
  FOR iter_round IN 1 .. v_total_rounds LOOP
    FOR iter_match IN 1 .. 9 LOOP
        dbms_output.put_line('iter_round: ' || iter_round);
        dbms_output.put_line('  iter_match: ' || iter_match);
      SELECT HOME_TEAM, AWAY_TEAM, iter_round , iter_match
      into l_home, l_away, l_round, l_match
      FROM (
        select home_team, away_team, dbms_random.value as rnd from all_possible_fixtures a

            left outer join 
                next_round_vw next_home on (a.home_team = next_home.club_id and nvl(next_home.next_round, 'H') = 'H' and next_home.round = iter_round - 1)
            
            left outer join
                next_round_vw next_away on (a.away_team = next_away.club_id and nvl(next_away.next_round, 'A') = 'A' and next_Away.round = iter_round - 1)

        --
        -- the teams cannot already be playing each other in this configuration
        --
        where  not exists ( select null from fixture where home_team||away_team = a.home_team||a.away_team)
          and  not exists ( select null from fixture where away_team||home_team = a.away_team||a.home_team)
        --
        -- these teams cannot be scheduled to play more than 9 games home or away
        --
          and  ( select count(*) from fixture where home_team = a.home_team ) < v_home_game_limit
          and  ( select count(*) from fixture where away_team = a.away_team ) < v_home_game_limit
        --
        -- the teams cannot have had two home games in the last two rounds
        --
          and (next_home.club_id is not null or iter_round = 1)
          and (next_away.club_id is not null or iter_round = 1)
        --
        -- these teams cannot already be in this round, either home or away
        --
          and not exists ( select null from fixture where round = iter_round and home_team = a.home_team)
          and not exists ( select null from fixture where round = iter_round and home_team = a.away_team)
          and not exists ( select null from fixture where round = iter_round and away_team = a.home_team)
          and not exists ( select null from fixture where round = iter_round and away_team = a.away_team)
        order by 3
      ) WHERE ROWNUM = 1;

      dbms_output.put_line('Round: '||lpad(l_round,2)||'  Match:'||lpad(l_match,2)||'   '||l_home||','||l_away);
      INSERT INTO FIXTURE(HOME_TEAM, AWAY_TEAM, ROUND, MATCH, YEAR)      values (l_home, l_away, l_round,l_match, v_year );

      COMMIT;
    END LOOP;
  END LOOP;
END;

BEGIN
  for i in 1 .. 200
  loop
    begin
      insertGames;
      exit;
    exception
      when no_data_found then
        if i = 200 then raise; end if;
    end;
  end loop;
END;
/


To confirm, the largest sequence is now three

with qry as (
select club_id, round, case when club.club_id = fix.home_team then 'H' else 'A' end as home_away
from club_details club
inner join fixture fix on club.club_id = fix.home_team or club.club_id = fix.away_team
)
select club_id, home_away, count(*) seq_count from (
select 
    qry.*
  , (row_number() over (partition by club_id order by round) - row_number() over (partition by club_id, home_Away order by round)) as grp
from qry
)
group by club_id, home_away, grp
order by 3 desc


CLUB_ID | HOME_AWAY | SEQ_COUNT
20 H 3
180 A 3
60 A 3
70 A 3
100 H 3
Chris Saxon
February 23, 2022 - 2:31 pm UTC

Glad you got it sorted
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.