Table Design and SQL query

Databases
1

Hi everyone I am learning PHP and mysql using sitepoints BYO database book by Kevin Yank 4th edition.

I’ve set my mind on a squash league as this is something I am interested in, understand how I want it to work and would be useful to me.

First of all, is it okay to have a table called GAMES that is created as follows:

CREATE TABLE game (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
type TEXT NOT NULL,
matchid INT NOT NULL,
player1id INT NOT NULL,
player2id INT NOT NULL,
player1score INT NOT NULL,
player2score INT NOT NULL
) DEFAULT CHARACTER SET utf8;

I think this is okay, as there is a ‘many-to-two’ relationship, perhaps this is just many-to-many.

A player will play in lots of games but a game will only ever have 2 players.

Now I am trying to obtain the id’s of all of the opponents that a player may have played in the past so they can look at their head-to-head record.

I am trying this query:

SELECT id FROM game
WHERE player1_id OR player2_id != 1;

This keeps returning all of the rows in that table yet when I use the following queries individually I can see that I get the correct results.

SELECT id FROM game
WHERE player1_id != 1;

SELECT id FROM game
WHERE player1_id != 2;

I guess in plain language I am trying to ‘Select all of the player ids from the games table in which player 1 took part, but exclude the player 1 id’.

Any ideas on why this is occuring would be greatly appreciated.

Thanks for your time and help.

2 
SELECT matchid
     , player1id
     , player1score
     , player2id 
     , player2score
  FROM game
 WHERE 1 IN (player1id,player2id)

:cool:

3

Just the player ids?


SELECT DISTINCT player2_id AS opponent_id
FROM game
WHERE player1_id = 1
UNION
SELECT DISTINCT player1_id
FROM game
WHERE player2_id = 1

This should give you the player ids of all opponents player 1 ever played against.

By the way, aren’t there doubles in squash?

4

Heh guys, thanks for both of those, that has been most helpful.

guido 2004, I actually want the players first name from the player table too.

CREATE TABLE player (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
dob DATE NOT NULL,
email TEXT NOT NULL,
password CHAR(32)
) DEFAULT CHARACTER SET utf8;

Also with regards to your comment on squash doubles, this does indeed exist although I have say I have never apart from myself on the odd occassion seen it played anywhere else but on TV, so for the moment I am not too concerned about this but it is a totally valid point.

It would also be helpful if you could confirm whether or not the table design for ‘game’ is correct.

The fact that I have match in there has been making me wonder if I need another table called match? I guess it would be something like

CREATE TABLE match (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
gameid INT NOT NULL
) DEFAULT CHARACTER SET utf8;

Actually that wouldn’t work. Both columns would need to form the primary key. I’m not sure if this new table is in fact necessary though?

Again thanks in advance for your time and help.