-1

I am using mysql when i try :

SELECT GROUP_CONCAT(score)
FROM DailyScore
WHERE team = '42'
AND Date IN('2013-08-01','2013-08-02','2013-08-03','2013-08-04')

It will return a result of :

50,100,75

Because team 42 has no score at '2013-08-02'

And I wish I can get :

50,,100,75

What sql query shall I use ?

Marc Audet
  • 46,011
  • 11
  • 63
  • 83
Theo Ymca
  • 109
  • 2
  • 10
  • possible duplicate of [Why MYSQL IN keyword not considering NULL values](http://stackoverflow.com/questions/10810391/why-mysql-in-keyword-not-considering-null-values) – Filipe Silva Aug 07 '13 at 20:50
  • I don't have time to type out some example code, but I think you'll find some good help in [this article](http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator). – Miklos Aubert Aug 07 '13 at 20:56

1 Answers1

5

If you mean that the score for 2013-08-02 is null then you should use IFNULL

SELECT GROUP_CONCAT(IFNULL(score, ''))
FROM DailyScore
WHERE team = '42'
AND Date IN('2013-08-01','2013-08-02','2013-08-03','2013-08-04')

If you mean there is not a record for 2013-08-02 for team 42, you should look at using a calendar table

SELECT GROUP_CONCAT(IFNULL(score, ''))
FROM DailyScore
RIGHT JOIN Calendar ON Calendar.Date = DailyScore.Date
                   AND team = '42'
AND Calendar.Date IN('2013-08-01','2013-08-02','2013-08-03','2013-08-04')

demo

As a side note it seems like you could also use BETWEEN

SELECT GROUP_CONCAT(IFNULL(score, ''))
FROM DailyScore
WHERE team = '42'
AND Date BETWEEN '2013-08-01' AND '2013-08-04'
T I
  • 9,785
  • 4
  • 29
  • 51
  • +1 for multi - answer, but in matter of an fact, he needs something in the middle -` between` with `calendar table` to have best result – jaczes Aug 07 '13 at 21:01
  • "calendar table" is a good reference. thx. the date is get from other query ,so a can't use "BETWEEN" – Theo Ymca Aug 07 '13 at 21:24
  • 1
    the query is not working. "team = '42'" should after the "ON" instead of after the "WHERE". – Theo Ymca Aug 07 '13 at 21:57
  • @TheoYmca yep you're right have updated my answer, alternatively you could do `WHERE team='42' OR team IS NULL` but I think the condition is better and more correct as part of the join clause. – T I Aug 07 '13 at 22:35