CODEWITHSUNDEEP
sql-serverssms
Vahe
Vahe

Reputation: 1841

MSSQL JOIN Query - Incorrect Syntax Near 'Value'

I have a query for which I have not visually seen syntax or formatting errors.

However upon execution, SSMS reports that there is a syntax error near 'Value'.

select * from sw.Users u 
join sw.Locations l 
join sw.Organizations o 
on u.LocationID = l.LocationID 
and l.OrganizationID = o.OrganizationID 
and o.OrganizationName = 'Value'

Error

Msg 102, Level 15, State 1, Line 38 Incorrect syntax near 'Value'.

Trying the following works with results:

select * from sw.Users u 
join sw.Locations l 
on u.LocationID = l.LocationID

Why is the first join query reporting the error?

Upvotes: 1

Views: 526

Answers (4)

Heinzi
Heinzi

Reputation: 172220

Your first join is missing a join condition. The syntax is: a JOIN b ON ..., not just a JOIN b.

If you are joining three tables, you need to do that in two steps, either

(a JOIN b ON ...) JOIN c ON ...

or

a JOIN (b JOIN c ON ...) ON ...

Note that the parenthesis are optional, which is probably the reason why the error message says "near Value" - SQL Server assumes that you mean to use the second syntax and misses the second ON at the end of your statement.

The first syntax is usually easier to read. Personally, I'd write your SQL statement as follows:

SELECT * 
  FROM sw.Users AS u 
       INNER JOIN sw.Locations AS l ON u.LocationID = l.LocationID 
       INNER JOIN sw.Organizations AS o ON l.OrganizationID = o.OrganizationID 
 WHERE o.OrganizationName = 'Value'

Each JOIN line adds another table, and its ON clause describes the relationship between the old result set and the new table. The actual filter criterion is in a WHERE clause.

Upvotes: 3

Reisclef
Reisclef

Reputation: 2148

You need to add the "on" after each join.

select * from sw.Users u 
join sw.Locations l 
on u.LocationID = l.LocationID 
join sw.Organizations o 
on l.OrganizationID = o.OrganizationID 
where o.OrganizationName = 'Value'

Have a look here for more information.

Upvotes: 0

Chandan Rai
Chandan Rai

Reputation: 10359

try restructuring like this

select * from sw.Users u 
join sw.Locations l    on u.LocationID = l.LocationID 
join sw.Organizations o on
l.OrganizationID = o.OrganizationID 
and o.OrganizationName = 'Value'

Upvotes: 0

Siyavash Hamdi
Siyavash Hamdi

Reputation: 3087

You need to use ON keyword after each (INNER)JOIN

SELECT  *
FROM    sw.Users            u
JOIN    sw.Locations        l   ON u.LocationID = l.LocationID 
JOIN    sw.Organizations    o   ON l.OrganizationID = o.OrganizationID
                                   AND o.OrganizationName = 'Value'

Upvotes: 0

Related Questions