CODEWITHSUNDEEP
sqlsql-server
arcee123
arcee123

Reputation: 211

JOIN with WHERE CLAUSE

I have this query:

Select 
    Tbl1.field1, 
    count (*) as total
from Tbl1
LEFT JOIN Tbl2 on Tbl1.geoid=Tbl2.field1
LEFT JOIN Tbl3 L on Tbl2.Field2 = L.Field2
and Tbl2.[Current Original Listing Date] >= Convert(datetime, '2017-08-01' )
GROUP BY Tbl1.field1

Tbl1.field1 has a static list I need to output the entirety regardless the count of Tbl3.

my problem is that as I tighten up the date in the where clause, I'm expecting more zero's in the count column, but when the count goes to zero, the record goes away.

what am I missing? Thanks.

Upvotes: 1

Views: 79

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

Place the date restriction within the join of the table it references:

SELECT
    Tbl1.field1, 
    count (*) as total
FROM Tbl1
LEFT JOIN Tbl2 ON Tbl1.geoid = Tbl2.field1
              AND Tbl2.[Current Original Listing Date] >= '20170801'
LEFT JOIN Tbl3 L on Tbl2.Field2 = L.Field2
GROUP BY Tbl1.field1

Not sure why you are joining tbl3 but if it is needed then include it.

Note if using CONVERT() to datetime from a literal then you should specify the style number otherwise you are relying on server settings. Otherwise the safest date literal in SQL Server is YYYYMMDD and then you don't need to use convert or cast.

Upvotes: 1

Related Questions