CODEWITHSUNDEEP
sqlsql-serverjoin
Chuck Norris
Chuck Norris

Reputation: 1003

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

Upvotes: 2

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You want a left join, but you have to be careful. Conditions on the second table need to be in the on clause:

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 LEFT JOIN
     Tiempo t
     ON u.username = t.empleado AND
        t.fecha >= '2016-08-01' AND t.fecha <= '2016-08-31'
GROUP BY u.id, t.empleado, CAST(MONTH(t.fecha) AS VARCHAR(2)), CAST(YEAR(t.fecha) AS VARCHAR(4))

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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))

Upvotes: 3

Related Questions