3

I have two tables (tblReps and tblDailyWorkingTime). The tblReps table, i.e., the first one, returns 37 rows, but when I JOIN it with the second table I get 36 rows as the second 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'
| improve this question | | | | |
8

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.

| improve this answer | | | | |
  • Thank you, Andriy that explanation was very helpful and makes sense to me. I have tested and I am now getting the expected result. – Mark Blackburn May 27 '19 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.