SQL Error Incorrect syntax near the keyword 'Left'

May 3, 2022

Trying to join 3 tables in order to segment customers who either opened or clicked in an email within the past year. I have tried the below query a multitude of different ways and am always getting a syntax error ‘Incorrect syntax near the keyword ‘Left” when trying to incorporate the EventDate in my Where clause. If I take out the Where clause it works fine. Wondering if anyone can help me troubleshoot why that is.

select Distinct
    drvr.EmailAddress,
    drvr.OCCAS_CD,
    drvr.Purchase_Date,
    drvr.Cnsmr_First_Nm,
    drvr.Cnsmr_Last_Nm,
    drvr.EmailAvail,
    drvr.cr_membership_status,
    drvr.subscriber_key
From
    [Test_2019 Anniversary Step 2] drvr
Left Join 
    _open o On o.SubscriberKey = drvr.Subscriber_Key
Where
    o.EventDate Between dateadd(year, -1, getdate()) and getdate()
Left Join 
    _Click c On c.SubscriberKey = drvr.Subscriber_Key
Where
    c.EventDate Between Dateadd(Year, -1, Getdate()) and Getdate()

>Solution :

Since this is all one SQL query, all the WHERE statements should be in the same place, after the join statements

select Distinct
    drvr.EmailAddress,
    drvr.OCCAS_CD,
    drvr.Purchase_Date,
    drvr.Cnsmr_First_Nm,
    drvr.Cnsmr_Last_Nm,
    drvr.EmailAvail,
    drvr.cr_membership_status,
    drvr.subscriber_key
From
    [Test_2019 Anniversary Step 2] drvr
Left Join 
    _open o On o.SubscriberKey = drvr.Subscriber_Key
Left Join 
    _Click c On c.SubscriberKey = drvr.Subscriber_Key
Where
    c.EventDate Between Dateadd(Year, -1, Getdate()) and Getdate()
AND
    o.EventDate Between dateadd(year, -1, getdate()) and getdate()

Should work.