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.

[quoted text, click to view]

You did not explain what do those tables and defined
fields represent and did not give any example data
(it also does not explain if there is any relation
between those tables), so I'm not sure how to interprete
this structure.


[quoted text, click to view]

What kind of statistics? For some score (where's that
score?)? For play time (where is it?)?

[quoted text, click to view]

Again: What does this data returned mean? (PS.: When you
use ASCII tables, then please make them look readable.)


[quoted text, click to view]

OK. Let's assume that you want to get a total count of
all records from "GameYellows" and "GameReds" for each player
from "Players" (I don't think it's not what you want, but
maybe it'll help you build your query):

SELECT
Players.id, Players.FullName,
ISNULL( gy.cnt, 0 ) AS yellows,
ISNULL( gr.cnt, 0 ) AS reds
FROM Players
LEFT OUTER JOIN (
SELECT Player_ID, COUNT(*) AS cnt
FROM GameYellows
GROUP BY Player_ID
) AS gy ON Players.id = gy.Player_ID
LEFT OUTER JOIN (
SELECT Player_ID, COUNT(*) AS cnt
FROM GameReds
GROUP BY Player_ID
) AS gr ON Players.id = gr.Player_ID
ORDER BY Players.id