Incorrect syntax near the keyword 'where' when converting MySQL to MSSQL

Question

So I am trying to convert some stuff from MySql to MSSQL 2012 the problem is I dont know Sql that well to know what is wrong with this statement. I get a response when this is ran against the MySql DB with the same information as the MSSQL DB has.

I am getting the error :

Incorrect syntax near the keyword 'where'. Msg: 156, Level: 15, State: 1, Procedure: , Line: 11

select distinct
    coalesce(s.state_prov, ls.state_prov, 'State Unknown') as state_prov 
from 
    circuit_id_locations cid
join
    location_states ls
left join
    location_states s 
on 
cid.state_prov = s.abreviation
where 
ls.idx = '99'
or 
cid.id = '99'

scragar · Accepted Answer · 2014-08-13 15:43:14Z

1

You have more JOINs than you do ON clauses, turn your normal join into a CROSS JOIN to make it work.

select distinct
    coalesce(s.state_prov, ls.state_prov, 'State Unknown') as state_prov 
from 
    circuit_id_locations cid
CROSS join
    location_states ls
left join
    location_states s 
on 
cid.state_prov = s.abreviation
where 
ls.idx = '99'
or 
cid.id = '99'

answered Aug 13, 2014 at 15:43

scragar

6,84530 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2136748 Over a year ago

Thank you! So just for my own curiosity is that just one of those fun little differences between MySql and MSSql. I want to dig in to understand why that worked on one and not the other. But I am sure that is a subject for me to dive into at another place. :)

scragar Over a year ago

MySQL typically makes a lot of assumptions on your behalf about what you want, handling conversion of types(so when you do '5'/2 it'll return 2.5, while SQL Server will complain that you can't treat varchars like numbers, MySQL does the same to boolean expressions), and even making ON clauses optional since you don't always want one(and unless you're doing an OUTER JOIN like your LEFT OUTER JOIN the join conditions can just as easily go in the where clause anyway). Other things you may run into are that SQL Server demands group by on non-aggregate return columns when grouping.

Matches · Accepted Answer · 2014-08-13 15:42:21Z

1

You have a join statement without an 'on statement'

>>>MISSING JOIN TYPE (inner/left/right/cross)<<< join
    location_states ls
>>>MISSING ON<<<

left join location_states s

answered Aug 13, 2014 at 15:42

Matches

1418 bronze badges

Collectives™ on Stack Overflow

Incorrect syntax near the keyword 'where' when converting MySQL to MSSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related