Raymond Raymond / .NET Programming

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

event 2009-11-06 visibility 948 comment 0 insights
more_vert
insights Stats

本文展示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
More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts