Problems using SQL Server JOIN-CodePudding

I have two tables with some equals fields and one field that makes the difference:

Table One - Ingresaron

DepartmentId Fecha_Lunes Entraron
------------ ----------- -----------
26           2022-08-01  1
26           2022-08-15  2
26           2022-08-22  3
26           2022-08-08  3

Table Two - Salieron

DepartmentId Fecha_Lunes Salieron
------------ ----------- -----------
26           2022-08-15  3
26           2022-08-22  4
26           2022-08-08  2
26           2022-08-29  1

I'm looking for query that returns a result set like this:

DepartmentId Fecha_Lunes Salieron   Entraron
------------ ----------- ---------- -----------
26           2022-08-01  null       1
26           2022-08-08  2          3
26           2022-08-15  3          1
26           2022-08-22  4          2
26           2022-08-29  1          null

But no matter what combinations of JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN I can not solve it, can you help me please?

CodePudding user response：

A full join would do:

select coalesce(ing.DepartmentId, s.DepartmentId) as DepartmentId,
   coalesce(ing.Fecha_lunes, s.Fecha_lunes) as Fecha_lunes,
   s.Saileron,
   ing.Entraron
from ingresaron ing
full join saileron s on ing.departmentId = s.DepartmentId and ing.Fecha_lunes = s.Fecha_lunes;

EDIT

2022-08-29 has no problem, it's not excluded. See the