1

I want to make a program that will show the attendances of an employee in one month. I can do that if just show the day that an employee was present, but i want to show the dates when an employee doesn't present (absence). I have MySQL Query like this

SELECT x.Tanggal
        , TIME(a.EventTime) AS TimeIn
FROM
(SELECT  Tanggal FROM (
SELECT DATE_ADD('2014-05-01', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) AS Tanggal FROM
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n1,
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n2,
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n3,
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n4,
(SELECT 0 AS num
   UNION ALL SELECT 1
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9) n5
) a
WHERE (Tanggal BETWEEN '2014-05-01' AND '2014-05-31')) x
LEFT JOIN VW_AttendanceIN a ON DATE(a.EventTime)=x.Tanggal
WHERE a.EmployeeID='90443'
AND (DATE(a.EventTime) BETWEEN '2014-05-01' AND '2014-05-31')
GROUP BY x.Tanggal

But, the result just give the present date like this

Result:

|   Tanggal  |  TimeIn  |
| 2014-05-01 | 08:00:00 |
| 2014-05-02 | 08:00:00 |
| 2014-05-05 | 08:00:00 |
| 2014-05-06 | 08:00:00 |
| 2014-05-07 | 08:00:00 |

The 3rd and 4th weren't listed i want the result like this

|   Tanggal  |  TimeIn  |
| 2014-05-01 | 08:00:00 |
| 2014-05-02 | 08:00:00 |
| 2014-05-03 |   NULL   |
| 2014-05-04 |   NULL   |
| 2014-05-05 | 08:00:00 |
| 2014-05-06 | 08:00:00 |
| 2014-05-07 | 08:00:00 |

Can anyone help me how to fix this?

christ2702
  • 473
  • 6
  • 12
  • 26
  • `AND (DATE(a.EventTime) BETWEEN '2014-05-01' AND '2014-05-31')` where makes it an inner join and probably move it to the joining clause. – Abhik Chakraborty Jun 04 '14 at 10:08
  • @AbhikChakraborty it gives the same result if i remove that clause – christ2702 Jun 04 '14 at 10:15
  • could you please provide the table structures and some data I think its similar to http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 I answered earlier. – Abhik Chakraborty Jun 04 '14 at 10:21

1 Answers1

2

Solution

You need to replace this:

LEFT JOIN VW_AttendanceIN a ON DATE(a.EventTime)=x.Tanggal
WHERE a.EmployeeID='90443'
AND (DATE(a.EventTime) BETWEEN '2014-05-01' AND '2014-05-31')

with the following: 

LEFT JOIN VW_AttendanceIN a ON DATE(a.EventTime)=x.Tanggal AND a.EmployeeID='90443'

Reason

References to a joined table in the WHERE clause filters out final result set rows without matching rows from the joined table, i.e. it does not allow a.EmployeeID or a.EventTime to be NULL. It's not the case if one places these references in the ON clause of the LEFT JOIN because it filters out not matching rows from the joined table and not from the final result set.

dened
  • 4,253
  • 18
  • 34