Unable to get the results from multiple tables

I have this schema. I am only interested in the ItemNumber and ExternalInvoiceNUmber. Using the code below

   Select
   StockItem.ItemNumber,
   PurchaseItem.Quantity,
    Purchase.ExternalInvoiceNumber,
    PurchaseItem.Delivered
     From
        StockItem Left Join
        PurchaseItem On PurchaseItem.fkStockItemId = StockItem.pkStockItemID Left Join
         Purchase On Purchase.pkPurchaseID = PurchaseItem.fkPurchasId

the result is not exactly what i want

           ItemNumber  Quantity  ExternalInvoiceNumber  Delivered
            item1        10         PO9993                10 
            item1        10         PO9994                0
            item1        10         PO9995                0

the problem is that i don't want any result that contains the records of the item has been delivered. I tried to use the code

         where      PurchaseItem.Delivered <> '0'

but then it won't show any items which only have 1 externaminvoicenumber and has been delivered. Basically, what i want is for the mssql to not display any records that has been delivered. However, if the PO has been delivered then it should show that item with the blank quanity and bla开发者_C百科nk externalinvoicenumber. I've been racking my brain about this for a week. Could you please help me?

You need to filter in the JOIN condition.

In the WHERE, it becomes an INNER JOIN

...
Left Join
PurchaseItem On 
          PurchaseItem.fkStockItemId = StockItem.pkStockItemID
          AND PurchaseItem.Delivered <> '0'
Left Join
...

Or, my preferred style to separate JOIN and filter

...
Left Join
(SELECT * FROM PurchaseItem WHERE Delivered <> '0') PurchaseItem 
        On  PurchaseItem.fkStockItemId = StockItem.pkStockItemID
Left Join
...

WITH DeliveredInvoice AS
    (SELECT StockItem.ItemNumber as item, 0 as quant, 
        0 as ein, 0 as del
        FROM StockItem 
        JOIN PurchaseItem ON PurchaseItem.fkStockItemId = StockItem.pkStockItemId
        JOIN Purchase ON Purchase.pkPurchaseId = PurchaseItem.fkPurchaseId
        WHERE PurchaseItem.Quantity > 0
        AND PurchaseItem.Quantity = PurchaseItem.Delivered),
    UndeliveredInvoice AS
    (SELECT StockItem.ItemNumber as item, PurchasedItem.Quantity as quant, Purchase.ExternalInvoiceNumber as ein, 
        PurchaseItem.Delivered as del
        FROM StockItem 
            JOIN PurchaseItem ON PurchaseItem.fkStockItemId = StockItem.pkStockItemId
            JOIN Purchase ON Purchase.pkPurchaseId = PurchaseItem.fkPurchaseId
        WHERE PurchaseItem.Delivered = 0)

SELECT item, quant, ein, del
FROM UndeliveredInvoice

UNION

SELECT item, quant, ein, del
FROM UndeliveredInvoice
WHERE item NOT IN (select distinct item from UndeliveredInvoice)