CODEWITHSUNDEEP
sqljoin
Kerieks
Kerieks

Reputation: 1064

sql query not bringing back correct records

I've got a sql query which should bring back records based on certain values not matching. In my BaseStock table I've got actualQty(int) and baseQty(int). In another table I've got the ordersQty (which is worked out based on the actualQty and baseQty). The orderQty will be baseQty - actualQty.

Placing the order it will take the baseQty - actualQty to make the orderQty. But if the order is already placed and the orderQty + actualQty = baseQty it should not bring back those records.

The query I've made doesn't bring back any records..... This is what I have done:

    SELECT BS.[ID],BS.[WarehouseNo],BS.[Customer],BS.[Site],BS.[VendorNo],BS.[Description],
  BS.[ActualQty],BS.[BaseQty], (BS.[BaseQty] - BS.[ActualQty]) AS PlaceOrderQty
   FROM [dbo].[StockControlBaseStock] AS BS
   RIGHT JOIN [dbo].[StockControlStockOrder] AS SO
   ON BS.ID = SO.BaseStockRef
   WHERE (BS.[ActualQty] + SO.OrderQTY) <> BS.[BaseQty]
   AND BS.[ActualQty] IS NOT NULL
   AND BS.[BaseQty] IS NOT NULL

Is there something I am doing wrong? any help will be greatly appreciated!!!

Upvotes: 0

Views: 75

Answers (1)

User999999
User999999

Reputation: 2520

Could you try the following solution?

SELECT BS.[ID],BS.[WarehouseNo],BS.[Customer],BS.[Site],BS.[VendorNo],BS.[Description],
BS.[ActualQty],BS.[BaseQty], (BS.[BaseQty] - BS.[ActualQty]) AS PlaceOrderQty
FROM [dbo].[StockControlBaseStock] AS BS
LEFT JOIN [dbo].[StockControlStockOrder] AS SO
ON BS.ID = SO.BaseStockRef
WHERE (BS.[ActualQty] + Iif(SO.OrderQTY IS NULL, 0, SO.OrderQTY)) <> BS.[BaseQty]
AND BS.[ActualQty] IS NOT NULL
AND BS.[BaseQty] IS NOT NULL

This query returns all orders without the adequate order placed

Upvotes: 1

Related Questions