联接操作中的 SQL 语法错误，其中 FROM 子句中的子查询

Question

我一直在抨击这个问题，我相信这是一个超级简单的答案。 在Excel 2016 VBA中，我有以下SQL字符串，但是在连接操作中出现语法错误。

SELECT c.code, p.description, p.weight, p.[pack size], p.ppb, p.[CP-UK], p.[CP-EU]
FROM((
SELECT *
FROM [Catalogue Info] as c
LEFT JOIN [Product Information] as p on c.code = p.code
)
LEFT JOIN [;database=C:mypathdb.accdb;PWD=1234].tbl as pl on c.code = pl.product_code
)
WHERE c.Category = 'VIENNOISERIE'
AND c.Sub_Cat_1 = 'CROISSANTS'
AND c.Sub_Cat_2 = ''
AND p.active = true
ORDER BY c.Page ASC, c.Page_Position ASC;

我尝试在子查询中使用第二个 LEFT JOIN，但我正在努力在 SELECT 子句中引用它。

有什么想法吗？

Answer 1

我认为这会做，

SELECT j.code, j.description, j.weight, j.[pack size], j.ppb, j.[CP-UK], j.[CP-EU]
FROM(
SELECT c.code, p.description, p.weight, p.[pack size], p.ppb, p.[CP-UK], p.[CP-EU],
c.Category,c.Sub_Cat_1,c.Sub_Cat_2,p.active,c.Page,c.Page_Position
FROM [Catalogue Info] as c
LEFT JOIN [Product Information] as p on c.code = p.code
)j
LEFT JOIN [;database=C:mypathdb.accdb;PWD=1234].tbl as pl on j.code = pl.product_code
WHERE j.Category = 'VIENNOISERIE'
AND j.Sub_Cat_1 = 'CROISSANTS'
AND j.Sub_Cat_2 = ''
AND j.active = true
ORDER BY j.Page ASC, j.Page_Position ASC;

Answer 2

假设查询外部数据库的方式是正确的语法

SELECT c.code, p.description, p.weight, p.[pack size], p.ppb, p.[CP-UK], p.[CP-EU]
FROM [Catalogue Info] as c
LEFT JOIN [Product Information] as p on c.code = p.code
LEFT JOIN [;database=C:mypathdb.accdb;PWD=1234].tbl as pl on c.code = pl.product_code
WHERE c.Category = 'VIENNOISERIE'
AND c.Sub_Cat_1 = 'CROISSANTS'
AND c.Sub_Cat_2 = ''
AND p.active = true
ORDER BY c.Page ASC, c.Page_Position ASC;

另外，当您在SELECT语句中不使用外部 db (.tbl as pl时，为什么要包含它？

Answer 3

使用以下代码

SELECT c.code, p.description, p.weight, p.[pack size], p.ppb, p.[CP-UK], p.[CP-EU]
FROM((
SELECT *
FROM [Catalogue Info] as c
LEFT JOIN [Product Information] as p on c.code = p.code
) as t
LEFT JOIN [;database=C:mypathdb.accdb;PWD=1234].tbl as pl on t.code = pl.product_code
) as t1
WHERE t1.Category = 'VIENNOISERIE'
AND t1.Sub_Cat_1 = 'CROISSANTS'
AND t1.Sub_Cat_2 = ''
AND pactive = true
ORDER BY Page ASC, Page_Position ASC;