Ttdfjt

I have 2 tables (Reps and DailyStartTimes).
the Rep Table returns 37 rows, but when I Join it with the 2nd table I get 36 rows as the 2nd table only has 36 rows that match the first table. How do I go about getting the 37 rows back with a Null result for the row that was not matched?

Here is the query that I have so far

SELECT 
 tblReps.[sName] AS 'RepName',
 tblReps.sNote AS 'Type',
 RIGHT(CAST(tblDaily.sDateAndTimeStart AS smalldatetime), 8) AS 'DayStarted'

FROM 
 [tblRepresentatives] AS tblReps

FULL OUTER JOIN 
 tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 

WHERE 
 tblDaily.sDate = CAST(GETDATE() AS DATE)
 AND tblReps.[sActive] = 'True'

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.