我有一个查询,我没有看到语法或格式错误。
但是,在执行时,SSMS 报告"值"附近存在语法错误。
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'
错误
消息 102,级别 15,状态 1,第 38 行 "值"附近的语法不正确。
尝试以下操作会产生结果:
select * from sw.Users u
join sw.Locations l
on u.LocationID = l.LocationID
为什么第一个联接查询报告错误?
您的第一个联接缺少联接条件。语法是:a JOIN b ON ...
,而不仅仅是a JOIN b
。
如果要联接三个表,则需要分两步完成,要么
(a JOIN b ON ...) JOIN c ON ...
或
a JOIN (b JOIN c ON ...) ON ...
请注意,括号是可选的,这可能是错误消息显示"接近值"的原因 - SQL Server 假定您要使用第二种语法,而错过了语句末尾的第二个语法ON
。
第一种语法通常更容易阅读。就个人而言,我会按如下方式编写您的SQL语句:
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'
每个 JOIN 行添加另一个表,其 ON 子句描述旧结果集和新表之间的关系。实际的筛选条件在 WHERE 子句中。
(INNER
)JOIN
之后使用ON
关键字
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'
尝试像这样重组
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'
您需要在每次连接后添加"on"。
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'
请点击此处了解更多信息。