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)
精彩评论