Access中的Inner join, Left join, Right join 嵌套连接查询语法

visibility 854 event 2009-11-06 access_time 26 days ago language 中文
more_vert

本文展示Access SQL中怎样写嵌套连接查询语句。

Access和SQLServer 的不一样,需要将子语句用括号包含,请注意下面示例中的括号

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3 [INNER JOIN [( ]tablex [INNER JOIN ...)] ON table3.field3 compopr tablex.fieldx)] ON table2.field2 compopr table3.field3) ON table1.field1 compopr table2.field2;

否则会造成诸如下面的错误:内联三个表

语法错误 (操作符丢失) 在查询表达式 'ur.UserID=u.UserID inner join [DT_Role] r on r.RoleID=ur.RoleID' 中。 在 .....

比如这里的语句:

Select r.RoleName from [User] u inner join [UserInRoles] ur on u.UserID=ur.UserID inner join [Role] r on ur.RoleID=r.RoleID where u.UserName= ? and ApplicationID is null

这在SQL Server中是不会出错的,但是在Acceess中则会出错,应该改为:

Select r.RoleName from [User] u inner join ([UserInRoles] ur inner join [Role] r on ur.RoleID=r.RoleID)on u.UserID=ur.UserID where u.UserName= ? and ApplicationID is null
info Last modified by Raymond 26 days ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts