0

Hi I hope you can help me with this problem:

I have these tables:

Games
id        name
1         Game1
2         Game2
3         Game3

Plays
game_id        user_id      numbers        date
1              1            2,12,34,56     2015-06-30 13:15:00
2              1            5,10,22,41     2015-06-30 13:15:00
3              1            1,7,11,34      2015-06-30 13:15:00
1              2            4,17,29,32     2015-06-30 13:15:00
1              1            2,9,19,45      2015-06-31 13:15:00
2              1            5,16,26,31     2015-06-31 13:15:00
3              1            4,12,16,34     2015-06-31 13:15:00
1              1            3,15,27,43     2015-06-01 13:15:00
1              1            1,7,14,29      2015-06-02 13:15:00
2              1            7,23,31,48     2015-06-02 13:15:00

I need to get all of user_id = 1 plays between today and 10 days in the past BUT if the user didn't play in one date the date should be returned anyway. The result should be something like this:

game_id      user_id       numbers       date
1            1             null          2015-06-26 00:00:00
2            1             null          2015-06-26 00:00:00
3            1             null          2015-06-26 00:00:00
same for days 27-28-29
1            1             2,12,34,56    2015-06-30 13:15:00
2            1             5,10,22,41    2015-06-30 13:15:00
3            1             1,7,11,34     2015-06-30 13:15:00
and so on

This is the query i'm using now but it only returns the dates that actually exist:

SELECT g.id, gam.name, g.played_numbers, g.date FROM plays AS g RIGHT JOIN games AS gam ON g.game_id = gam.id AND g.user_id = 1 AND g.date BETWEEN '2015-05-04 15:00:00' AND '2015-06-03 15:00:00'

Thanks in advance.

Gerardo
  • 159
  • 1
  • 12
  • 2
    It may be easier to fill in the missing days in your PHP code. Or you could do 10 `SELECT`s with 10 dates and glue them together with `UNION`s. Maybe someone else has a better idea. – Tomaso Albinoni Jun 02 '15 at 21:01
  • Thanks @TomasoAlbinoni, I'll try filling the dates as you suggest until I come up with another idea. Thanks again! – Gerardo Jun 03 '15 at 15:36

0 Answers0