sql - 获取空值加入 3 个表

Question

下面是查询，我加入了 3 个表供应商是主表。实际情况是我想要表应付账款表中的所有数据，尽管它不在采购订单表中，所以我使用 FULL Outer 与供应商和采购订单加入，但供应商详细信息并未与应付账款数据相抵触虽然供应商密钥可用。

SELECT ISNULL(dbo.Supplier.supplier_key,dbo.Fact_AccountPayables.supplier_key) AS supplier_key,
                  dbo.Supplier.Supplier,
                  dbo.Supplier.Name,
                  dbo.Supplier.Status, 
                  dbo.Supplier.AddressCode, 
                  dbo.Supplier.Address,
                  dbo.Supplier.HouseNo,
                  dbo.Supplier.Street,
                  dbo.Supplier.City, 
                  dbo.Supplier.Country,
                  dbo.Supplier.ZipCode,
                  dbo.Supplier.StartDate,
                  dbo.Supplier.CreditLimit, 
                  dbo.Supplier.FinancialGroup,
                  dbo.Supplier.LastTransactionDate, 
                  dbo.Fact_PurchaseOrder.Company, 
                  ISNULL(dbo.Fact_PurchaseOrder.[Purchase Order],dbo.Fact_AccountPayables.[PO Number]) AS PurchaseOrder,
                  ISNULL( dbo.Fact_PurchaseOrder.Sequence,dbo.Fact_AccountPayables.Line) AS POSequence, 
                  dbo.Fact_PurchaseOrder.[Order Quantity], 
                  dbo.Fact_PurchaseOrder.[Per Purchase Unit], 
                  dbo.Fact_PurchaseOrder.[Per Quantity Price],
                  dbo.Fact_PurchaseOrder.[Purchase price unit],
                  dbo.Fact_PurchaseOrder.[Total Order Amount],
                  dbo.Fact_PurchaseOrder.Currency, 
                  dbo.Fact_PurchaseOrder.[Rate Date], 
                  dbo.Fact_PurchaseOrder.[Actual Receipt Date],
                  dbo.Fact_PurchaseOrder.[Receipt No],
                  dbo.Fact_PurchaseOrder.[Receipt Sequence], 
                  dbo.Fact_PurchaseOrder.[Received Quantity],
                  dbo.Fact_PurchaseOrder.[Approved Quantity], 
                  dbo.Fact_PurchaseOrder.[Purchase Office], 
                  dbo.Fact_PurchaseOrder.[Invoice Number], 
                  dbo.Fact_PurchaseOrder.[Invoice Date], 
                  dbo.Fact_PurchaseOrder.[Invoice Quantity],
                  dbo.Fact_PurchaseOrder.[Invoice Amount],
                  dbo.Fact_AccountPayables.InvoiceNumber, 
                  dbo.Fact_AccountPayables.Type AS InvoiceType, 
                  dbo.Fact_AccountPayables.[Order Type] AS OrderInvoiceType,
                  dbo.Fact_AccountPayables.AP_Balance_EUR, 
                  dbo.Fact_AccountPayables.[Invoice Amount_EUR],
                  dbo.Fact_AccountPayables.supplier_key AS EXPR2,
                  dbo.Fact_AccountPayables.[IntercompanyTrade Order No] AS EXPR23, 
                  dbo.Fact_AccountPayables.[IntercompanyTrade Line Number] AS EXPR24,
                  dbo.Fact_AccountPayables.[Intercompany Trade Financial Company] AS EXPR25, 
                  dbo.Fact_AccountPayables.[Intercompany Trade Purchase Company] AS EXPR26,
                  dbo.Fact_AccountPayables.InvoiceNumber,
                  dbo.Fact_AccountPayables.DueDate,
                  dbo.Fact_AccountPayables.DocDate, 
                  dbo.Fact_PurchaseOrder.[Order Date],
                  dbo.Fact_AccountPayables.[Invoice Amount_EUR],
                  (CASE WHEN dbo.Fact_PurchaseOrder.[Receipt No] = ' ' THEN dbo.Fact_PurchaseOrder.[Total Order Amount]
                        WHEN  dbo.Fact_PurchaseOrder.[Receipt No] != ' ' and dbo.Fact_AccountPayables.InvoiceNumber IS NULL then dbo.Fact_PurchaseOrder.[Total Order Amount] END) AS ORDERBALANCE,
                  (dbo.Supplier.CreditLimit -(ORDERBALANCE + dbo.Fact_AccountPayables.[Invoice Amount_EUR])) AS Availablecredit
    FROM            dbo.Supplier 
            LEFT OUTER JOIN dbo.Fact_PurchaseOrder ON dbo.Supplier.supplier_key = dbo.Fact_PurchaseOrder.buyfrom_supplier_key
            full OUTER JOIN dbo.Fact_AccountPayables ON dbo.Fact_AccountPayables.supplier_key = dbo.Supplier.supplier_key AND
                                                        dbo.Fact_AccountPayables.[PO Number] = dbo.Fact_PurchaseOrder.[Purchase Order]   AND 
                                                    dbo.Fact_AccountPayables.[PO Line] = dbo.Fact_PurchaseOrder.Sequence

输出是这样的：

标签： sqlsql-servertsql

Answer 1

如果某些行没有供应商，则会在 Fact_PurchaseOrder 中进行过滤。您需要对所有表进行 FULL OUTER JOIN，以获取 AccountPayables 的所有行的数据，无论它们是否有供应商。

FROM  dbo.Supplier 
FULL OUTER JOIN dbo.Fact_PurchaseOrder ON dbo.Supplier.supplier_key = dbo.Fact_PurchaseOrder.buyfrom_supplier_key
FULL OUTER JOIN dbo.Fact_AccountPayables ON dbo.Fact_AccountPayables.supplier_key = dbo.Supplier.supplier_key AND
                                                        dbo.Fact_AccountPayables.[PO Number] = dbo.Fact_PurchaseOrder.[Purchase Order]   AND 
                                                    dbo.Fact_AccountPayables.[PO Line] = dbo.Fact_PurchaseOrder.Sequence