Cross Join then left Join (Uknown column)

Question

What query would I do if this code is working on PHPMyAdmin SQL:

SELECT 
DATE_FORMAT(d.date, '%b %e %Y') date, 
u.employee_id, ai.time_in, 
ao.time_out 
FROM (SELECT date FROM hris_timein UNION SELECT date FROM hris_timeout order by date asc) d 
CROSS JOIN hris_users u 
LEFT JOIN hris_timein ai ON u.employee_id = ai.employee_id AND ai.date = d.date 
LEFT JOIN hris_timeout ao ON u.employee_id = ao.employee_id AND ao.date = d.date

Output: see output

But when I use this code in my project, it displays an error:

Error description   Unknown column 'd.date' in 'on clause'

Answer 1

The query looks correct, try it with quoted identifiers.

SELECT 
            DATE_FORMAT(d.`date`, '%b %e %Y') `date`, 
            u.`employee_id`,
            ai.`time_in`, 
            ao.`time_out` 
    FROM
           (
               SELECT
                           `date`
                   FROM
                           `hris_timein`
               UNION
               SELECT
                           `date`
                   FROM
                           `hris_timeout`
           ) d 
        CROSS JOIN
           `hris_users` u 
        LEFT JOIN
           `hris_timein` ai
               ON u.`employee_id` = ai.`employee_id` AND ai.`date` = d.`date` 
        LEFT JOIN
           `hris_timeout` ao
               ON u.`employee_id` = ao.`employee_id` AND ao.`date` = d.`date`;