全部加入上的MS Access [英] Full Join on MS Access

问题描述

所以我试图做一个完整的连接上的MS Access 2003，但只是发现了它不支持它。所以，我想以我的两个选择语句，然后使用LEFT JOIN加盟一个，做一个联盟与相同的语句，但与右连接。访问给了我一个错误，说有什么不对的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 

抱歉，如果这不是在code画幅，访问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!

编辑：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;

错误：加盟前$不支持的e $ pssion。第一件code是很好的左外连接。我想两个左联接和工作。它只是没有采取我的权利加入...

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尝试。您需要首先创建一个可行的加盟，我不能当场有什么不对您提供的样本。

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.

访问是否接受这种简化版本？

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.

如果它的工作，看到这样的权利是否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子句;这是未经测试的空中code。但如果这也适用，结合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屋！