MySQL - Subquery with IF NULL doesn't follow the where clause when combining 2 rows into 1

Question

i am trying to combine the pair of attendance which includes row IN and OUT in one row using Subquery and IF NULL

I have this app table of user 710, this is use for time in and time out of a user

id  userid  mode  date_entry    time_entry
1   710     In    2022-03-23    2022-03-23 07:05:58
2   710     Out   2022-03-24    2022-03-24 07:03:25
3   710     In    2022-03-24    2022-03-24 07:00:05
4   710     Out   2022-03-24    2022-03-24 18:06:23
5   710     In    2022-03-25    2022-03-25 07:10:16
6   710     In    2022-03-26    2022-03-26 07:11:57
7   710     Out   2022-03-26    2022-03-26 18:18:12

And using this query:

SELECT a.`id`,a.`userid`,a.`date_entry`,
(SELECT MIN(b.`time_entry`) FROM erpweb.`app` b WHERE b.`date_entry` = a.`date_entry` AND b.`mode` = 'IN' AND b.`userid` = a.`userid`) AS 'IN',
IFNULL(
    (SELECT b.`time_entry` FROM erpweb.`app` b WHERE b.`date_entry` = a.`date_entry` AND b.`mode` = 'OUT' AND b.`userid` = a.`userid` LIMIT 1),
    (SELECT MIN(b.`time_entry`) FROM erpweb.`app` b WHERE b.`date_entry` = DATE_ADD(a.`date_entry`,INTERVAL 1 DAY) AND b.`mode` = 'OUT' AND b.`userid` = a.`userid`)
    )AS 'OUT'
FROM erpweb.`app` a
WHERE a.`date_entry` BETWEEN '2022-03-23' 
  AND '2022-03-26'
GROUP BY a.`date_entry`,a.`userid`

I got this result. you can see in the row ID 5 it takes the OUT of 2022-03-26 which doesn't match in my where clause which is "WHERE b.date_entry = a.date_entry" in line 4 of my sql query because i don't have OUT for 2022-03-25

id  userid  date_entry  IN                    OUT
1   710     2022-03-23  2022-03-23 07:05:58   2022-03-24 07:03:25
2   710     2022-03-24  2022-03-24 07:00:05   2022-03-24 07:03:25
5   710     2022-03-25  2022-03-25 07:10:16   2022-03-26 18:18:12
6   710     2022-03-26  2022-03-26 07:11:57   2022-03-26 18:18:12

The expected result must be: NULL in row ID 5 because the userid 710 doesn't have time OUT for the date 2022-03-25

id  userid  date_entry  IN                    OUT
1   710     2022-03-23  2022-03-23 07:05:58   2022-03-24 07:03:25
2   710     2022-03-24  2022-03-24 07:00:05   2022-03-24 07:03:25
5   710     2022-03-25  2022-03-25 07:10:16   NULL
6   710     2022-03-26  2022-03-26 07:11:57   2022-03-26 18:18:12

Answer 1

SELECT *
FROM table t1
LEFT JOIN table t2 
    ON t1.time_entry < t2.time_entry 
   AND t2.mode = 'Out'
   AND NOT EXISTS ( SELECT NULL
                    FROM table t3
                    WHERE t1.time_entry < t3.time_entry
                      AND t3.time_entry < t2.time_entry )
WHERE t1.mode = 'In'

I.e. select rows pairs where earlier row is IN and later row is OUT and no any row between them. If no such OUT row for definite IN row then add NULL row.

Add the condition by userid if needed.