完全加入 MS Access [英] Full Join on MS Access

问题描述

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

so I'm trying to do a full join on MS Access 2003 but just found out it did not support it. So I tried taking my two select statements and then joining one using LEFT join and making a UNION with the same statement but with a RIGHT join. Access gave me an error saying that there is something wrong with the JOIN command. Heres some 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 

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

sorry if this is not in code format, access sql i guess is just normal text. this one is with only the left join. if you have any ideas, please say so. Thanks!

两步连接，

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;

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

error: join expression not supported. The first piece of code was good for left outer join. i tried two left joins and that worked. its just not taking my right join...

推荐答案

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

Your ultimate goal is to emulate a FULL OUTER JOIN, but your first hurdle is that Access' database engine complains about your LEFT JOIN attempt. You need to first create a workable JOIN, and I can't spot what's wrong with the sample you provided.

Access 是否接受此简化版本?

Does Access accept this simplified version?

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 ... 只是该查询是否正常工作并返回正确的行.

For the moment, we're not concerned about the field list or ORDER BY ... simply whether that query works without error and returns the correct rows.

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

If it does work, see whether this RIGHT JOIN returns the remaining rows you need.

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 个查询合并为一个:

You may need to change the WHERE clause; that was untested air code. But if that also works, combine the 2 queries into one:

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;

这篇关于完全加入 MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！