FULL OUTER JOIN不起作用

Question

无论是否有来自该供应商的采购，我都需要退回所有供应商。查询当前仅返回供应商进行采购的记录。

    SELECT vendors.NAME, 
       Iif([fundingsourceid] = 10, [amount], 0)        AS Credit, 
       Iif(( [fundingsourceid] = 2 ) 
            OR ( [fundingsourceid] = 3 ), [amount], 0) AS EBT, 
       Iif([fundingsourceid] = 4, [amount], 0)         AS [Match], 
       cardpurchases.updateddate 
FROM   vendors 
       FULL OUTER JOIN cardpurchases 
                    ON cardpurchases.vendorid = vendors.vendorid 
       LEFT JOIN cardfundings 
              ON cardpurchases.cardfundingid = cardfundings.cardfundingid 
       INNER JOIN marketevents 
               ON cardpurchases.marketeventid = marketevents.marketeventid 
       INNER JOIN markets 
               ON marketevents.marketid = markets.marketid 
WHERE  (cardpurchases.updateddate >= '10/22/2014' OR cardpurchases.updateddate IS NULL)
   AND (cardpurchases.updateddate < '10/23/2014' OR cardpurchases.updateddate IS NULL)
   AND (markets.marketid = 47 OR markets.marketid IS NULL)
ORDER  BY vendors.NAME 

Answer 1

尽管您稍后在查询中指定了FULL OUTER JOIN，但您正在根据cardpurchases表中的列限制结果集，这会导致没有cardpurchase的供应商消失。

您可以执行以下任一操作：

WHERE
((cardpurchases.updateddate >= '10/22/2014'
    AND cardpurchases.updateddate < '10/23/2014')
        OR cardpurchases.updateddate IS NULL)           
 AND markets.marketid = 47 

或

FROM   vendors 
   LEFT JOIN cardpurchases 
       ON cardpurchases.vendorid = vendors.vendorid
          AND cardpurchases.updateddate >= '10/22/2014'
          AND cardpurchases.updateddate < '10/23/2014')

Answer 2

您需要在WHERE子句中说明NULL：

WHERE  (cardpurchases.updateddate >= '10/22/2014' OR cardpurchases.updateddate IS NULL)
       AND (cardpurchases.updateddate < '10/23/2014' OR cardpurchases.updateddate IS NULL)
       AND (markets.marketid = 47 OR markets.marketid IS NULL)

您还应该使用括号来控制联接，这样以后的INNER JOIN就不会破坏它：

FROM   vendors 
       FULL OUTER JOIN (cardpurchases 
              LEFT JOIN cardfundings 
                     ON cardpurchases.cardfundingid = cardfundings.cardfundingid 
              INNER JOIN marketevents 
                      ON cardpurchases.marketeventid = marketevents.marketeventid 
              INNER JOIN markets 
                      ON marketevents.marketid = markets.marketid)
       ON cardpurchases.vendorid = vendors.vendorid 

这只是部分问题，因为LEFT JOIN和FULL会遇到同样的问题。