0

I have three tables :

games:
game_id int(11) NO PRI auto_increment
archive_moves varchar(3000) NO
chat varchar(3000) NO

players:
player_id int(11) NO PRI auto_increment
username varchar(30) NO UNI
password varchar(30) NO
email varchar(30) NO UNI
activate bit(1) NO b'0'
hash varchar(32) NO
game_number int(10) NO MUL 0

playersgames:
playergame_id int(11) NO PRI auto_increment
player_id int(11) NO MUL
game_id int(11) NO MUL
game_number int(10) NO MUL 0

playersgames.game_id is foreign key for games.game_id
playersgames.player_id is foreign key for player.player_id

What I want is game_number gets exactly the same value from players to playersgames. So I created a foreign key between players.game_number and playersgames.game_number and "cascade on update" it. The problem is when I change value from players.game_number, the value for playersgames gets the highest value from players.game_number and there is no more connection between players.player_id and his player.game_number.

The idea behind this is to get playersgames.player_id only if game_number < 5. In other words, a player cannot has more than 5 games. I'm open to other solution if it's more convenient.

Thanks !

bobHihi
  • 53
  • 8

1 Answers1

0

You don't need the game_number column in your playergames table or in the players table. Instead you can run the following query:

SELECT
    p.player_id,
    COUNT(g.playergame_id) AS numGames
FROM
    players p
LEFT JOIN
    playersgames g ON p.player_id = g.player_id
GROUP BY
    p.player_id

The result will give you the player ids and the number of games the player has. See also: MySQL joins and COUNT(*) from another table.

Progman
  • 16,827
  • 6
  • 33
  • 48
  • Thanks, I totally forget the count method. Here is my final request : "SELECT player_id FROM players WHERE player_id NOT IN (SELECT player_id FROM (SELECT player_id, COUNT(playergame_id) AS numGames, game_id FROM playersgames GROUP BY player_id) AS S1 WHERE game_id IN(SELECT game_id FROM playersgames WHERE player_id=7) OR numGames > 5) LIMIT 1;". I use it for create a matchmaking between players. Conditions are : max 5 games per player and only one match for peer of two players. Thanks a lot again !!! – bobHihi Oct 09 '17 at 20:01