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

'2022-03-026' ? is that a typo in your question or a typo in your code? — P.Salmon, Jun 08 '22 at 12:33
what does `select version();` show? the client version doesn't matter — ysth, Jun 08 '22 at 12:39
Consider Lateral: https://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql you can join the "In" set to the "out" set and ensure the time entry in the outset is > then entry in the in set and it's < the next in entry using lead time_entry in the "in" set — xQbert, Jun 08 '22 at 12:40
Pay attention - in your desired output the ranges in 1st and 2nd rows overlaps. *The correct result must be: NULL in row ID 5 because the userid 710 doesn't have time OUT for the date 2022-03-25* - The same about the source row 1 (*because the userid 710 doesn't have time OUT for the date 2022-03-23*), but you find OUT for this row - why? — Akina, Jun 08 '22 at 13:33
@AKINA point is that out time_entry for row 2 is after in time_entry in row 3 - which seems problematic - perhaps a typo ? — P.Salmon, Jun 08 '22 at 13:47
@PaulIversonCortez has this been addressed or are you yet looking for a more complete response? The out isn't null becuase your date limit is on the base table of operations, not the selects used to generate the "out" value — xQbert, Jun 15 '22 at 16:11

Akina · Answer 1 · 2022-06-08T13:03:45.857

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.

edited Jun 08 '22 at 13:03

answered Jun 08 '22 at 12:57

Akina

39,301
5
14
25

1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bcc41fbb10cb1f40148fd06ca58d1270 Doesn't seem to return expected results Doesn't matter the expected results don't have a pattern... yours does. – xQbert Jun 08 '22 at 13:12
@xQbert The output matches the task **completely**. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f2588b3cdcb839e0a0e313aa8fbcedd0 - investigate. – Akina Jun 08 '22 at 13:28
Sorry but this doesn't give the expected result. – Paul Iverson Cortez Jun 08 '22 at 13:34
@PaulIversonCortez Read my comment to the question. The "expected result" is illogical. It contradicts the explanations. – Akina Jun 08 '22 at 13:35
the row ID 5 goes to the first condition of IF NULL, not in the condition where i add DATE_ADD(a.`date_entry`,INTERVAL 1 DAY) – Paul Iverson Cortez Jun 08 '22 at 13:43
@PaulIversonCortez Do you want to say that your logic allows the situation where the man comes IN twice and then goes OUT twice??? – Akina Jun 08 '22 at 13:48
Maybe Yes, I have 3 Scenario that represents each of rows in the given table. Row 1 & 2: The Man Time IN 03-23-2022 07:05 AM and Time OUT 03-24-2022 07:03 AM because of OT Row 3 & 4: The Man Time IN 03-24-2022 07:00 AM and Time OUT 03-24-2022 06:06 PM Row 5 : The Man Time IN 03-25-2022 07:10 AM without Time OUT due to saving ERROR that is why it doesn't save Row 6 & 7: same scenario in row 3 and 4. Thank you for giving me some useful answer. im happy that someone is trying to help me – Paul Iverson Cortez Jun 08 '22 at 13:58
@PaulIversonCortez The man cannot move IN at row 3 - he is IN already according row 1 and will go OUT after 3 minutes of the time stored in row 3 according to row 2. – Akina Jun 08 '22 at 15:54
@akina. one can if one walks out w/o carding out on someone else's out. That's the problem with this type of data. you have to be able to handle the edge cases and why a specific pattern/solution will not always work. But if the rules are well defined, one can design a method to do it; but it's seldom clean due to the exception processing. – xQbert Jun 08 '22 at 18:37
@xQbert If one can walk through w/o carding then the case when a lot of OUTs matches one IN (and backward) within 1-day period is possible. And this state may be accompanied by the amounts difference. And it is impossible to create only one unambiguous IN-OUT pairs in this case. The task is administrative one, not programmatical. From practice, one penalty is enough so that a person (and a lot of persons who have heard about it) never forgets to mark the passage through the turnstile again. – Akina Jun 09 '22 at 04:46
I let this one go. the data is the data. the request is the request. This is going to happen no matter what. In the case of a fire drill or actual fire, do you want people to wait in line and burn while they wait their turn to card out. there's always exceptions, edge cases. Logically I agree an in should have an out; but since the data isn't that way,, we try to meet the request we can't have data conform to our needs, its' the other way around. – xQbert Jun 15 '22 at 16:07

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

1 Answers1