当我在陈述时使用案例时，“双重结果”

Question

Table msabsensi

+--------+-----------+-------+----------+----------+------------+------------+
| id     | nik       | nik_b | in_hr    | out_hr   | in_date    | out_date   |
+--------+-----------+-------+----------+----------+------------+------------+
| 262230 | 216065459 | 5459  | 07:42:00 | 16:37:00 | 2017/10/25 | 2017/10/25 |
| 263430 | 216065459 | 5459  | 07:40:00 | 16:29:00 | 2017/10/26 | 2017/10/26 |
| 264610 | 216065459 | 5459  | 07:38:00 | 20:01:00 | 2017/10/27 | 2017/10/27 |
| 267550 | 216065459 | 5459  | 19:40:00 | 08:38:00 | 2017/10/29 | 2017/10/30 |
| 268870 | 216065459 | 5459  | 23:50:00 | 09:06:00 | 2017/10/30 | 2017/10/31 |
| 270067 | 216065459 | 5459  | 00:00:00 | 08:32:00 | NULL       | 2017/11/01 |
| 271359 | 216065459 | 5459  | 23:50:00 | 08:12:00 | 2017/11/01 | 2017/11/02 |
| 272614 | 216065459 | 5459  | 23:48:00 | 08:47:00 | 2017/11/02 | 2017/11/03 |
| 274119 | 216065459 | 5459  | 00:00:00 | 20:10:00 | NULL       | 2017/11/04 |
| 274975 | 216065459 | 5459  | 07:46:00 | 00:00:00 | 2017/11/05 | NULL       |
+--------+-----------+-------+----------+----------+------------+------------+

Table mstanggal

+-----+------------+
| id  | tanggal    |
+-----+------------+
| 298 | 2017/10/25 |
| 299 | 2017/10/26 |
| 300 | 2017/10/27 |
| 301 | 2017/10/28 |
| 302 | 2017/10/29 |
| 303 | 2017/10/30 |
| 304 | 2017/10/31 |
| 305 | 2017/11/01 |
| 306 | 2017/11/02 |
| 307 | 2017/11/03 |
| 308 | 2017/11/04 |
| 309 | 2017/11/05 |
+-----+------------+

i have a query for view attendance (msabsensi) data on database when in_date is null then use out_date

SELECT c.tanggal, b.in_date, b.out_date, b.in_hr, b.out_hr, b.nik from mstanggal c
left outer join msabsensi b on c.tanggal = (CASE WHEN c.tanggal = b.in_date THEN b.in_date ELSE b.out_date END) 
where c.tanggal = '2017-11-01' and b.nik = '216065459'

but a result was double

+-----------+------------+-----------+----------+----------+-----------+
| tanggal   | in_date    | out_date  | in_hr    | out_hr   | nik       |
+-----------+------------+-----------+----------+----------+-----------+
| 11/1/2017 | 10/31/2017 | 11/1/2017 | 23:46:00 | 08:32:00 | 216065459 |
| 11/1/2017 | 11/1/2017  | 11/2/2017 | 23:50:00 | 08:12:00 | 216065459 |
+-----------+------------+-----------+----------+----------+-----------+

correct result is the second record how to display only that ?

+-----------+-----------+-----------+----------+----------+-----------+
| 11/1/2017 | 11/1/2017 | 11/2/2017 | 23:50:00 | 08:12:00 | 216065459 |
+-----------+-----------+-----------+----------+----------+-----------+

Answer 1

use COALESCE() or IFNULL()

SELECT c.tanggal
 , b.in_date
 , b.out_date
 , b.in_hr
 , b.out_hr
 , b.nik
FROM mstanggal c
LEFT JOIN msabsensi b ON c.tanggal = coalesce(b.in_date, b.out_date)
WHERE c.tanggal = '2017-11-01' AND b.nik = '216065459'