add date to every name-CodePudding

hello please help me to solve this,, i am using mysql.. and I think, needing a full outer join to solve it,.
this is my query:

SELECT
  e.NIK,
  e.name,
  a.dt
FROM
  employee e
    LEFT JOIN
  attandence a
    ON e.NIK=a.NIK
WHERE
  month(a.dt)=12 AND year(a.dt)=2021
GROUP BY
  e.NIK

this is the result:

NIK	name	dt
001	ana	23/12/2021
001	ana	24/12/2021
001	ana	26/12/2021
001	ana	27/12/2021
002	susi	23/12/2021
002	susi	24/12/2021
002	susi	25/12/2021
002	susi	26/12/2021

but i need to join it one more time with this table : holidayTable

id	mark	dt
1	off_day	22/12/2021
2	chrismast	25/12/2021

I've tried using left/right/cross join, but it doesn't work

The result I want is like this:

NIK	name	dtWork	holiday
001	ana	null	22/12/2021
001	ana	23/12/2021	null
001	ana	24/12/2021	null
001	ana	null	25/12/2021
001	ana	26/12/2021	null
001	ana	27/12/2021	null
002	susi	null	22/12/2021
002	susi	23/12/2021	null
002	susi	24/12/2021	null
002	susi	25/12/2021	25/12/2021
002	susi	26/12/2021	null

CodePudding user response：

A union where the first part gets attendence and the second gets holidays (with as you thought a cross join) including a dummy column to assist ordering

select e.id,employeelastname,a.dt attendence,null holiday,a.dt as orderdt
from   employees e
left   join attendence a on a.nik = e.id 
union
select e.id,employeelastname,null,h.dt holiday,h.dt
from   employees e
cross  join   holidays h
order by id,orderdt;

CodePudding user response：

You need a calendar table to achieve the output. As of now I used union between holidaytable and attendance as Calendar.

You can achieve your desired result with below query.

SELECT e.nik,
       e.name,
       a.dt AS dtWork,
       h.dt AS holiday
FROM   employee e
       CROSS JOIN(SELECT dt
                  FROM   holidaytable
                  UNION
                  SELECT dt
                  FROM   attendance)cal
                 LEFT JOIN attendance a
                        ON e.nik = a.nik
                           AND a.dt = cal.dt
                 LEFT JOIN holidaytable h
                        ON h.dt = cal.dt
WHERE  a.dt IS NOT NULL
        OR h.dt IS NOT NULL
ORDER  BY nik ASC,
          cal.dt ASC

SQLFiddle: Try it here