Marlind Parllaku Marlind Parllaku - 5 months ago 9
MySQL Question

I have this query that is driving me crazy

I have this damn query that I'm trying to figure out but it driving me totally crazy cause I can't find a way to start building it.
This is the table structure on which I will query data.

----------------------------------------------------------------------------
| id | id_user | id_game| id_question | user_answer | answer_time | points |
----------------------------------------------------------------------------


the table is called
game_user_answers
and I'm supposed to find the winner of a quiz game.
I have another table called
games
in which i store id game and the
game_start_time
.
If the answer has 0 points than the user didn't answer correctly.

Basically what I want to do is to get the id_user that has more points combined from all answers he's given and his last answers time is closest to the game_start_time.The game id and the game_start_time will be passed as parameters to the function in which the query will be executed so don't worry about them.

Please help me out.

Answer

This is not the answer, but the start point for you.

Here is sqlfiddle: http://sqlfiddle.com/#!9/197dd9/1

You should add more data into that fiddle. After that use my query to see some results and play with that query until you get to the closest result of what you expect to get. And once you get to the point when you can't get your goal, come back with your fiddle and query prepared and ask community for the help again.

SELECT 
   g.*,
   gua.*
FROM games g
LEFT JOIN game_user_answers gua
ON g.id = gua.id_game
  AND gua.answer_time>g.game_start_time
  AND points>0
WHERE g.id = 1 
  AND g.game_start_time = '2016-01-01 00:00:00'
Comments