First of all, the correct type of join to use in this case is a left join:
...
FROM
[tblRepresentatives] AS tblReps
LEFT OUTER JOIN
tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
...
In your specific case, you could still use FULL
because this condition in WHERE
would turn it into a left join anyway:
tblReps.[sActive] = 'True'
But it is better to express the intent accurately.
The same effect that turns your full join into a left join is actually responsible for the missing 37th row that you expected. More specifically, this other WHERE
condition:
tblDaily.sDate = CAST(GETDATE() AS DATE)
transforms your join further into an inner join. That is why the 37th row is missing from the output.
The reason for that happening is this. The FROM
clause returns a null in tblDaily.sDate
for the unmatched row. Because the WHERE
clause logically executes after the FROM
, the above-mentioned predicate excludes the unmatched row, since NULL = CAST(GETDATE() AS DATE)
does not evaluate to True.
What you need to do instead is move the sDate
condition to the ON
subclause:
...
FROM
[tblRepresentatives] AS tblReps
LEFT OUTER JOIN
tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
AND tblDaily.sDate = CAST(GETDATE() AS DATE)
WHERE
tblReps.[sActive] = 'True'
That way the right-hand side of the join is filtered on tblDaily.sDate = CAST(GETDATE() AS DATE)
before the join takes place. Consequently, the query will return the expected 37 rows from tblReps
complemented either with matching data from tblDaily
or with nulls.