Avatar of Nico2011
Flag for United Kingdom of Great Britain and Northern Ireland asked on

MSSQL Join - Incorrect Syntax near the keyword 'where'

Hello Experts,

I have an issue with the foillowing MSSQL Query, which returns the error below - it is a rather complex query (in my opinion!), but I need to link it to a booking calendar so we only get properties with availability showing...:

select * from (select row_number() over (order by distance) rn, * from (select * from (SELECT ID as XID, PayMonthly, ccaccepted, pool, sattv, disabled_access, internet, restaurants, shops, beach, golf, skiing, tennis, hiking, for_rent, for_sale, price_low, rental_currency, rental_USD, rental_EUR, rental_GBP, Problem, Property_Name, Town, Region, Property_Type, Property_Description, short_description, bedrooms, sleeps, photo, photo_two, photo_three, thumb, lat, lng, (3960 * acos( cos( radians(43.64701) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(7.00241) ) + sin( radians(43.64701) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas) v WHERE for_rent = 1 AND Problem = 0 AND distance <= 10 AND price_low IS NOT NULL) q) x JOIN Diary on XID = Diary.PropID where NOT(StartDate between '7-Jan-2012' and '28-Jul-2012')
Microsoft OLE DB Provider for SQL Server

 error '80040e14'

Incorrect syntax near the keyword 'where'.

/villa-results-list.asp, line 112

THANKS in advance.
Microsoft SQL Server 2008ASP

Avatar of undefined
Last Comment

8/22/2022 - Mon

FROM Villas) v WHERE  there'sa V in line8

Thanks, but that v is required.  If you take away everything after and including the word JOIN, the query qorks, but it's when I join the tables that it falls over...

Your problem appears to be the last WHERE.

where NOT(StartDate between '7-Jan-2012' and '28-Jul-2012')

it is wrong.

It should be where ( StartDate NOT between '7-Jan-2012' and '28-Jul-2012')
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thanks very much.