Chuck Norris Chuck Norris - 3 months ago 7
SQL Question

Querying tables: complex join

I have this query and it does a great job when there are matching rows on table

Tiempo
.

The problem is that what I really need to see are the no-matching rows in
Tiempo
, that exists also in
Usuario
too.

SELECT u.Id, t.empleado, CAST(MONTH(t.fecha) AS VARCHAR(2)) AS MonthNumber, CAST(YEAR(t.fecha) AS VARCHAR(4)) AS YearNumber, SUM(t.horas) AS Horas
FROM Usuario u
INNER JOIN Tiempo t ON u.username = t.empleado
WHERE fecha >= '2016-08-01' and fecha <= '2016-08-31'
GROUP BY u.id, t.empleado, CAST(MONTH(t.fecha) AS VARCHAR(2)), CAST(YEAR(t.fecha) AS VARCHAR(4))


This is the output:

Id empleado MonthNumber YearNumber Horas
86 username1 8 2016 96
95 username2 8 2016 80
99 username3 8 2016 47
102 username4 8 2016 85


And this is what I'm looking for:

Id empleado MonthNumber YearNumber Horas
86 username1 8 2016 96
95 username2 8 2016 80
99 username3 8 2016 47
102 username4 8 2016 85
102 username5 8 2016 null (or 0)



EDIT: Sample Data: download

Answer

Assuming the data for the missing row exists in your table, you do a LEFT JOIN instead of an INNER JOIN:

SELECT u.Id,
       COALESCE(t.empleado, 'NA')
       COALESCE(CAST(MONTH(t.fecha) AS VARCHAR(2)), 'NA') AS MonthNumber,
       COALESCE(CAST(YEAR(t.fecha) AS VARCHAR(4)), 'NA') AS YearNumber,
       SUM(t.horas) AS Horas
FROM Usuario u
LEFT OUTER JOIN Tiempo t
    ON u.username = t.empleado AND
       fecha >= '2016-08-01' AND
       fecha <= '2016-08-31'
GROUP BY u.id,
         t.empleado,
         CAST(MONTH(t.fecha) AS VARCHAR(2)),
         CAST(YEAR(t.fecha) AS VARCHAR(4))
Comments