使用嵌套连接进行访问的完全外连接
使用嵌套连接进行访问的完全外连接
提问于 2019-09-07 00:36:25
我正在尝试一个完整的外部连接解决方法来访问。我的代码目前使用一个表，但是当我尝试嵌套其他的左/右连接时，我得到一个“3075Syntax Error”。我相信Access希望我在某个地方加引号或括号，但我已经尝试了几种不同的方法，并进行了研究，但似乎无法掌握。任何帮助都将不胜感激！谢谢。
SQL = ""
SQL = SQL & "SELECT "
SQL = SQL & " t.* "
SQL = SQL & "FROM "
SQL = SQL & " ( "
SQL = SQL & " SELECT "
SQL = SQL & " [SummaryTbl].[WORK_ITEM_NMB] "
SQL = SQL & " , [SummaryTbl].[PROGRAM] "
SQL = SQL & " , [SummaryTbl].[WORK_ITEM_TYPE] "
SQL = SQL & " , [ParentChildTbl].[HasAssocWI] "
SQL = SQL & " FROM "
SQL = SQL & " SummaryTbl "
SQL = SQL & " LEFT JOIN "
SQL = SQL & " ParentChildTbl "
SQL = SQL & " ON "
SQL = SQL & " [SummaryTbl].
[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB] "
SQL = SQL & " LEFT JOIN "
SQL = SQL & " ObjectsAffectedTbl "
SQL = SQL & " ON "
SQL = SQL & " [SummaryTbl].
[WORK_ITEM_NMB] = [ObjectsAffectedTbl].[WORK_ITEM_NMB] "
SQL = SQL & " "
SQL = SQL & " UNION "
SQL = SQL & " "
SQL = SQL & " SELECT "
SQL = SQL & " [ParentChildTbl].
[WORK_ITEM_NMB] "
SQL = SQL & " , [SummaryTbl].[PROGRAM] "
SQL = SQL & " , [SummaryTbl].[WORK_ITEM_TYPE] "
SQL = SQL & " , [ParentChildTbl].[HasAssocWI] "
SQL = SQL & " FROM "
SQL = SQL & " SummaryTbl "
SQL = SQL & " RIGHT JOIN "
SQL = SQL & " ParentChildTbl "
SQL = SQL & " ON "
SQL = SQL & " [SummaryTbl].
[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB] "
SQL = SQL & " RIGHT JOIN "
SQL = SQL & " ObjectsAffectedTbl "
SQL = SQL & " ON "
SQL = SQL & " [SummaryTbl].
[WORK_ITEM_NMB] = [ObjectsAffectedTbl].[WORK_ITEM_NMB] "
SQL = SQL & " ) "
SQL = SQL & " t "
SQL = SQL & " WHERE "
SQL = SQL & " t.[WORK_ITEM_NMB] > 700"
Updated:
I added parenthesis as follows:
SQL = SQL & " FROM "
SQL = SQL & " (SummaryTbl "
SQL = SQL & " RIGHT JOIN "
SQL = SQL & " ParentChildTbl "
SQL = SQL & " ON "
SQL = SQL & " [SummaryTbl].
[WORK_ITEM_NMB] = [ParentChildTbl].[WORK_ITEM_NMB]) "
SQL = SQL & " RIGHT JOIN "
SQL = SQL & " ParentChildTbl "
SQL = SQL & " ON "
SQL = SQL & " [ParentChildTbl].
[WORK_ITEM_NMB] = [ObjectsAffectedTbl].[WORK_ITEM_NMB]) "
SQL = SQL & " ) "我认为这些都是正确的地方，因为现在我在Union子句中得到了错误，而不是From子句。但我不确定现在该如何处理Union子句。
回答 1
Stack Overflow用户
发布于 2019-09-07 02:14:04
在MS Access SQL中，多个JOIN需要括号对。为了简洁性和可读性，下面还使用了表别名：
SELECT
t.*
FROM
(
SELECT
s.[WORK_ITEM_NMB]
, s.[PROGRAM]
, s.[WORK_ITEM_TYPE]
, p.[HasAssocWI]
FROM
(
(SummaryTbl s
LEFT JOIN
ParentChildTbl p
ON
s.[WORK_ITEM_NMB] = p.[WORK_ITEM_NMB]
)
LEFT JOIN
ObjectsAffectedTbl o
ON
s.[WORK_ITEM_NMB] = o.[WORK_ITEM_NMB]
)
UNION
SELECT
p.[WORK_ITEM_NMB]
, s.[PROGRAM]
, s.[WORK_ITEM_TYPE]
, p.[HasAssocWI]
FROM
(
(ObjectsAffectedTbl o
LEFT JOIN
ParentChildTbl p
ON
o.[WORK_ITEM_NMB] = p.[WORK_ITEM_NMB]
)
LEFT JOIN
SummaryTbl s
ON
o.[WORK_ITEM_NMB] = s.[WORK_ITEM_NMB]
)
) t
WHERE
t.[WORK_ITEM_NMB] > 700顺便说一下，考虑将上面的内容保存在.sql (即文本文件)中，并读取到VBA字符串中，以避免连接和引用处理：
Function ReadSQL() As String
Dim LoadFileStr As String
With CreateObject("Scripting.FileSystemObject")
LoadFileStr = .OpenTextFile("C:\Path\To\File.sql", 1).readall
End With
ReadSQL = LoadFileStr
End Sub
Sub mySub
...
SQL = ReadSQL()
...
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57825750
复制相关文章
相似问题
腾讯云开发者
