Doing multiple joins on one table

I have those Tables:

CREATE TABLE GameYellows(

id INT NOT NULL AUTO_INCREMENT,

AddedDate DATETIME NOT NULL,

Player_ID INT NOT NULL,

)

CREATE TABLE GameReds(

id INT NOT NULL AUTO_INCREMENT,

AddedDate DATETIME NOT NULL,

Player_ID INT NOT NULL,

)

CREATE TABLE GameSubstitutes(

id INT NOT NULL AUTO_INCREMENT,

OutPlayer_ID INT NOT NULL,

InPlayer_ID INT NOT NULL,

)

CREATE TABLE Players(

id INT NOT NULL AUTO_INCREMENT,

FullName VARCHAR(250) NOT NULL,

)

What i need is actually to get a table of statistics for each player in the
players table.

meaning i need to return a table like this:

id | yellows | reds | substitutes_out | substitutes_in

---------------------------------------------------------------------

1 | 2 | 1 | 5 | 2

2 | 4 | 0 | 0 | 3

3 | 1 | 2 | 0 | 7



please help me solve this one, having hard time using join.

thanks, Yaniv.


.

Relevant Pages
Loading