SQL query, need help

[kickoutbettman]

I want to display the SUM of shot for all HOLE played in 2009 (my date field are 2009-05-05 so I can use LIKE "%2009%")

But the date field is only in the GAME table.

 

TABLE game

FIELD idGame, date, idPlayer

 

TABLE hole

FIELD idGame, idPlayer, shot

 

I tried to do : (at least to display all the holes even if its not 2009)

SELECT * FROM game WHERE idJoueur = 2 INNER JOIN hole ON hole.idGame = game.idGame

 

I get the error message :

Error of syntax 'INNER JOIN hole ON hole.idGame = game.idGame

 

Where am I wrong ?

 

Thank you

[Mchl]

(my date field are 2009-05-05 so I can use LIKE "%2009%")

 

Do not store dates as strings. Use DATE datatype.

 

SELECT * FROM game INNER JOIN hole ON hole.idGame = game.idGame WHERE game.idJoueur = 2

 

[Ken2k7]

First of all, what is idJoueur? Is that the idPlayer? But of which table? And like Mchl said, don't store dates as strings.

 

You have to put the INNER JOIN before the WHERE clause.

 

I would do something like this provided that idJoueur is the idPlayer and your date is correctly changed to datatype of DATE.

SELECT SUM(g.shot) AS sum_shots
FROM game g
INNER JOIN hole h
     ON (h.idGame = g.idGame)
WHERE DATE_SUB(CURDATE(), INTERVAL 0 YEAR) <= g.date
     AND g.idPlayer = 2

[kickoutbettman]

Actually my date field is a date type field..

i'm trying your query Ken.

 

Let you know in a few minutes

[kickoutbettman]

It returns NULL when using 0 YEAR in the date condition.

If I use 1 YEAR it returns the SUM of the last 365 days.

How could I return only result for 2009 ?

 

Thanks for your help

[Ken2k7]

SELECT SUM(g.shot) AS sum_shots
FROM game g
INNER JOIN hole h
     ON (h.idGame = g.idGame)
WHERE YEAR(g.date) = YEAR(CURDATE())
     AND g.idPlayer = 2

?