Mysql – SELECT that is null if there is one more register in this date that is not null

MySQL

I'm trying to make a query that I can't produce…

This is my table capturainfo

captureid   int(11)
userid      int(11) 
date        date
dateinit    datetime
datefin     datetime

I need to select all rows that have datefin=null and is not the last row of the day ( ie there is one more row with date is the same day but dateinit1<dateinit2.

I just can't do it.

Here are some attempts I've made:

The basic one:

SELECT * FROM capturainfo where date < "2015-02-27" 
AND datefin is null

it gets me all datefin is null, but I can't know if there is one more row in the same day.

SELECT * FROM capturainfo a where date < "2015-02-27" 
AND datefin is null 
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.datefin<= b.datefin) AND a.userid = b.userid )

… Well this last one doesn't work.

here is the data sample

+-----------+--------+------------+---------------------+---------------------+
|     id    | userid |    date    |      dateinit       |     datefin         |
+-----------+--------+------------+---------------------+---------------------+
|    0001   | 2034   | 2015-02-20 | 2015-02-20 11:41:31 | 2015-02-20 20:16:07 |
|    0002   | 1789   | 2015-02-20 | 2015-02-20 12:41:31 | 2015-02-20 13:43:34 |
|    0003   | 1432   | 2015-02-20 | 2015-02-20 12:55:33 |         NULL        |
|    0004   | 1432   | 2015-02-20 | 2015-02-20 13:44:21 | 2015-02-20 13:45:31 |
|    0005   | 1444   | 2015-02-20 | 2015-02-20 14:41:31 |         NULL        |
+-----------+--------+------------+---------------------+---------------------+

In this case, query should return id 0003 because user 1432 have made a posterior register in the same day

Anybody knows how to do it?

Best Answer

You had one closing parentheses too many in the latter query. You also have columns date and datefin and the distinction between the two is not clear. If you want to do a comparison per day, you have to make sure both rows are from the same day in the inner query (a.date = b.date) and there has to exist b.dateinit where a.dateinit is smaller (<) not smaller or equal (<=). You also erroneously compare datefin columns in the subquery when you should compare dateinit columns. It seems you want to get rows from dates older than 2015-02-27:

SELECT * FROM capturainfo a where date < "2015-02-27" 
AND datefin is null 
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.date = b.date AND a.dateinit < b.dateinit AND a.userid = b.userid )
Related Question