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 |
+-----------+-----------+-----------+----------+----------+-----------+
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'
Why are you using case? Do you only want to get the record where c.tanggal = b.out_date if c.tanggal <> b.in_date? Or do you only want to see records where c.tanggal <> b.in_date.
CASE is getting any record where c.tanggal = b.in_date or c.tanggal = b.out_date. For the first record, the second case is true and for the second record, the first case is true.
If you need to only pick up on the second case if there are no results from the first case, I would suggest adding another join:
SELECT c.tanggal, ifnull(b.in_date, d.in_date), ifnull(b.out_date, d.out_date), ifnull(b.in_hr,d.in_hr), ifnull(b.out_hr, d.out_hr), ifnull(b.nik,d.nik)
from mstanggal c
left outer join msabsensi b on c.tanggal = b.in_date and b.nik = '216065459'
left outer join msabsensi d on c.tanggal = d.out_date and d.nik = '216065459'
where c.tanggal = '2017-11-01'