sql - 完全加入 MS Access

Question

所以我试图在 MS Access 2003 上进行完全加入，但发现它不支持它。因此，我尝试使用我的两个 select 语句，然后使用 LEFT join 加入一个，并使用相同的语句创建一个 UNION，但使用 RIGHT join。 Access 给了我一个错误，说 JOIN 命令有问题。这里有一些sql...

SELECT tbl_Vendors.VendorName, tbl_Inventory.ItemNum, 
tbl_Inventory.Color,  tbl_Inventory.InInventory, 
tbl_Inventory.OutInventory, 
(tbl_Inventory.Stocks +   tbl_Inventory.InInventory - 
tbl_Inventory.OutInventory) AS Balance,  
tbl_Inventory.Weight, tbl_Inventory.CF,   
(tbl_Inventory.Weight *Balance) AS TotalWeight, 
(tbl_Inventory.CF * Balance) AS TotalCF, 
tbl_Inventory.NoteOrder, tbl_ItemHistory.orderDate, 
tbl_ItemHistory.POHistory, tbl_ItemHistory.InorOut, 
tbl_ItemHistory.Unit
FROM (tbl_Vendors INNER JOIN tbl_Inventory 
    ON tbl_Vendors.vid = tbl_Inventory.VendorID)
LEFT JOIN tbl_ItemHistory 
ON tbl_Inventory.ItemNum = tbl_ItemHistory.ItemNum
ORDER BY tbl_Inventory.ItemNum, tbl_ItemHistory.orderDate 

抱歉，如果这不是代码格式，我猜 Access sql 只是普通文本。这个只有左连接。如果您有任何想法，请说出来。谢谢!

编辑:2步连接，

SELECT tbl_Vendors.VendorName, tbl_Inventory.ItemNum, tbl_Inventory.Color, 
tbl_Inventory.InInventory, tbl_Inventory.OutInventory, 
(tbl_Inventory.Stocks+tbl_Inventory.InInventory-tbl_Inventory.OutInventory) AS Balance, 
tbl_Inventory.Weight, tbl_Inventory.CF, (tbl_Inventory.Weight*Balance) AS TotalWeight,    
(tbl_Inventory.CF*Balance) AS TotalCF, tbl_Inventory.NoteOrder, tbl_ItemHistory.orderDate, 
tbl_ItemHistory.POHistory, tbl_ItemHistory.InorOut, tbl_ItemHistory.Unit
FROM (tbl_Vendors INNER JOIN tbl_Inventory ON tbl_Vendors.vid = tbl_Inventory.VendorID) LEFT JOIN
tbl_ItemHistory ON tbl_Inventory.ItemNum = tbl_ItemHistory.ItemNum;
UNION ALL
SELECT tbl_Vendors.VendorName, tbl_Inventory.ItemNum, tbl_Inventory.Color, 
tbl_Inventory.InInventory, tbl_Inventory.OutInventory, 
(tbl_Inventory.Stocks+tbl_Inventory.InInventory-tbl_Inventory.OutInventory) AS Balance, 
tbl_Inventory.Weight, tbl_Inventory.CF, (tbl_Inventory.Weight*Balance) AS TotalWeight,    
(tbl_Inventory.CF*Balance) AS TotalCF, tbl_Inventory.NoteOrder, tbl_ItemHistory.orderDate, 
tbl_ItemHistory.POHistory, tbl_ItemHistory.InorOut, tbl_ItemHistory.Unit
FROM (tbl_Vendors INNER JOIN tbl_Inventory ON tbl_Vendors.vid = tbl_Inventory.VendorID) RIGHT  
JOIN tbl_ItemHistory ON tbl_Inventory.ItemNum = tbl_ItemHistory.ItemNum;

错误:不支持连接表达式。第一段代码适用于左外连接。我尝试了两个左连接并且有效。它只是没有接受我的正确加入......

Answer 1

您的最终目标是模拟 FULL OUTER JOIN，但您的第一个障碍是 Access 的数据库引擎提示您的 LEFT JOIN 尝试。您需要首先创建一个可行的 JOIN，我无法发现您提供的示例有什么问题。

Access 是否接受此简化版本？

SELECT *
FROM
    (tbl_Vendors AS vend
    INNER JOIN tbl_Inventory AS inv
    ON vend.vid = inv.VendorID)
    LEFT JOIN tbl_ItemHistory AS hist
    ON inv.ItemNum = hist.ItemNum;

目前，我们不关心字段列表或 ORDER BY ... 只是该查询是否正常工作并返回正确的行。

如果它确实有效，请查看此 RIGHT JOIN 是否返回您需要的剩余行。

SELECT *
FROM
    (tbl_Vendors AS vend
    INNER JOIN tbl_Inventory AS inv
    ON vend.vid = inv.VendorID)
    RIGHT JOIN tbl_ItemHistory AS hist
    ON inv.ItemNum = hist.ItemNum
WHERE inv.ItemNum Is Null;

您可能需要更改 WHERE 子句；那是未经测试的空气代码。但如果这也有效，请将 2 个查询合并为一个:

SELECT *
FROM
    (tbl_Vendors AS vend
    INNER JOIN tbl_Inventory AS inv
    ON vend.vid = inv.VendorID)
    LEFT JOIN tbl_ItemHistory AS hist
    ON inv.ItemNum = hist.ItemNum
UNION ALL
SELECT *
FROM
    (tbl_Vendors AS vend
    INNER JOIN tbl_Inventory AS inv
    ON vend.vid = inv.VendorID)
    RIGHT JOIN tbl_ItemHistory AS hist
    ON inv.ItemNum = hist.ItemNum
WHERE inv.ItemNum Is Null;