0

I'm doing an assignment where I am to make an sql-database of a tournament result. Players can be added by their name, and when the database has at least two or more players who has not already been assigned to a match, two players should be matched against each other.

For instance, if the tables currently are empty I add Joe as a player. I then also add James and since the table then has two players, who also are not in the matches-table, a new row in the matches-table is created with their p_id set to left_player_P_id and right_player_P_id.

I thought it would be a good idea to create a function and a trigger so that every time a row is added to the player-table, the sql-code would run and create the row in the matches as needed. I am open to other ways of doing this.

I've tried multiple different approaches including SQL - Insert if the number of rows is greater than and Using IF ELSE statement based on Count to execute different Insert statements but I am now at a loss.

Problematic code:

This approach returns a syntax error.

IF ((select count(*) from players_not_in_any_matches) >= 2)
  begin
    insert into matches values (
      (select p_id from players_not_in_any_matches limit 1),
      (select p_id from players_not_in_any_matches limit 1 offset 1)
      )
  end;

Alternative approach (still problematic code):

This approach seems more promising (but less readable). However, it inserts even if there are no rows returned inside the where not exists.

insert into matches (left_player_p_id, right_player_p_id)
select
    (select p_id from players_not_in_any_matches limit 1),
    (select p_id from players_not_in_any_matches limit 1 offset 1)
where not exists (
    select * from players_not_in_any_matches offset 2
);

Tables

CREATE TABLE players (
  p_id serial PRIMARY KEY,
  full_name text
  );

CREATE TABLE matches(
  left_player_P_id integer REFERENCES players,
  right_player_P_id integer REFERENCES players,
  winner integer REFERENCES players
  );

Views

-- view for getting all players not currently assigned to a match
create view players_not_in_any_matches as
  select * from players
   where p_id not in (
   select left_player_p_id from matches
   ) and
   p_id not in (
      select right_player_p_id from matches
      );
Community
  • 1
  • 1
Runar
  • 245
  • 3
  • 17

1 Answers1

1

Try:

insert into matches (left_player_p_id, right_player_p_id)
select p1.p_id, p2.p_id
from players p1
join players p2
on p1.p_id <> p2.p_id
and not exists(
   select 1 from matches m
   where p1.p_id in (m.left_player_p_id, m.right_player_p_id)
)
and not exists(
   select 1 from matches m
   where p2.p_id in (m.left_player_p_id, m.right_player_p_id)
)
limit 1

Anti joins (not-exists operators) in the above query could be further simplified a bit using LEFT JOINs:

insert into matches (left_player_p_id, right_player_p_id)
select p1.p_id, p2.p_id
from players p1
join players p2
left join matches m1
on p1.p_id in (m1.left_player_p_id, m1.right_player_p_id)
left join matches m2
on p2.p_id in (m2.left_player_p_id, m2.right_player_p_id)    
where m1.left_player is null
  and m2.left_player is null
limit 1

but in my opinion the former query is more readable, while the latter one looks tricky.

krokodilko
  • 35,300
  • 7
  • 55
  • 79