mysql 'IN' trouble when empty record

Question

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 ?

possible duplicate of Why MYSQL IN keyword not considering NULL values — Filipe Silva
– Filipe Silva, Commented Aug 7, 2013 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. — Miklos Aubert
– Miklos Aubert, Commented Aug 7, 2013 at 20:56

T I · Accepted Answer · 2013-08-07 22:34:15Z

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'

edited Aug 7, 2013 at 22:34

answered Aug 7, 2013 at 20:56

T I

9,9634 gold badges31 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jaczes Over a year ago

+1 for multi - answer, but in matter of an fact, he needs something in the middle -` between` with calendar table to have best result

Theo Ymca Over a year ago

"calendar table" is a good reference. thx. the date is get from other query ,so a can't use "BETWEEN"

Theo Ymca Over a year ago

the query is not working. "team = '42'" should after the "ON" instead of after the "WHERE".

T I Over a year ago

@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.

Collectives™ on Stack Overflow

mysql 'IN' trouble when empty record

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related