需要帮助在MS Access中实施完全外部联接 [英] Need help implementing a Full Outer Join in MS Access

问题描述

我无法使查询在Access中正常工作.我需要对dbo_cardpurchases和dbo_vendors进行完全外部联接，以便所有所有供应商都将出现在查询中，而不管是否在该供应商处进行了购买.但是Access不支持完整的外部联接.我还能怎么做?

I'm having trouble getting a query to work properly in Access. I need a full outer join on dbo_cardpurchases and dbo_vendors so that all all vendors will appear in the query regardless of whether a purchase was made at that vendor. But Access doesn't support full outer joins. How else can I do this?

SELECT dbo_vendors.name, 
       Iif([fundingsourceid] = 10, [amount], "")        AS Credit, 
       Iif(( [fundingsourceid] = 2 ) 
            OR ( [fundingsourceid] = 3 ), [amount], "") AS EBT, 
       Iif([fundingsourceid] = 4, [amount], "")         AS [Match], 
       dbo_cardpurchases.updateddate, 
       dbo_markets.marketid 
FROM   (((dbo_cardpurchases 
          LEFT JOIN dbo_vendors 
                 ON dbo_cardpurchases.vendorid = dbo_vendors.vendorid) 
         LEFT JOIN dbo_cardfundings 
                ON dbo_cardpurchases.cardfundingid = 
                   dbo_cardfundings.cardfundingid) 
        INNER JOIN dbo_marketevents 
                ON dbo_cardpurchases.marketeventid = 
                   dbo_marketevents.marketeventid) 
       INNER JOIN dbo_markets 
               ON dbo_marketevents.marketid = dbo_markets.marketid 
ORDER  BY dbo_vendors.name; 

推荐答案

如Wikipedia文章中有关联接此处，用于示例表

As mentioned in the Wikipedia article on joins here, for sample tables

[员工]

LastName    DepartmentID
----------  ------------
Heisenberg            33
Jones                 33
Rafferty              31
Robinson              34
Smith                 34
Williams            NULL

和[部门]

DepartmentID  DepartmentName
------------  --------------
          31  Sales         
          33  Engineering   
          34  Clerical      
          35  Marketing     

完整的外部联接

SELECT *
FROM employee FULL OUTER JOIN department
    ON employee.DepartmentID = department.DepartmentID;

可以使用三个SELECT语句的UNION ALL来模拟

.因此，在Access中您可以做到

can be emulated using a UNION ALL of three SELECT statements. So, in Access you could do

SELECT dbo_employee.LastName, dbo_employee.DepartmentID,
       dbo_department.DepartmentName, dbo_department.DepartmentID
FROM dbo_employee
INNER JOIN dbo_department ON dbo_employee.DepartmentID = dbo_department.DepartmentID

UNION ALL

SELECT dbo_employee.LastName, dbo_employee.DepartmentID,
       NULL, NULL
FROM dbo_employee
WHERE NOT EXISTS (
    SELECT * FROM dbo_department
             WHERE dbo_employee.DepartmentID = dbo_department.DepartmentID)

UNION ALL

SELECT NULL, NULL,
       dbo_department.DepartmentName, dbo_department.DepartmentID
FROM dbo_department
WHERE NOT EXISTS (
    SELECT * FROM dbo_employee
             WHERE dbo_employee.DepartmentID = dbo_department.DepartmentID)

但是，由于要在SQL Server中使用链接表，因此您只能使用Access传递查询并使用T-SQL执行真正的"完全外部联接:

However, since you are using linked tables into SQL Server you can just use an Access pass-through query and perform a "real" FULL OUTER JOIN using T-SQL:

直通查询始终会生成不可更新的记录集，但是针对使用UNION ALL的链接表的本机Access查询将生成一个无论如何都无法更新的记录集，所以为什么不利用它的速度和简便性呢?只是使用SQL Server运行查询?

Pass-through queries always produce recordsets that are not updateable, but a native Access query against linked tables that uses UNION ALL is going to produce a recordset that is not updatable anyway, so why not take advantage of the speed and simplicity of just using SQL Server to run the query?

这篇关于需要帮助在MS Access中实施完全外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！