I am trying to pull rows where the Net_Qty is /- 300 of the Order_Qty, but I think I might just be missing something with the syntax.

SELECT DISTINCT
    o.Ord_No,
    odf.OrdFuel_Order_Qty,
    odf.OrdFuel_Deliv_Net_Qty
FROM Order_Details_Fuel odf
JOIN Orders o ON odf.OrdFuel_Ord_Key = o.Ord_Key
WHERE odf.OrdFuel_Deliv_Net_Qty >= (300   odf.OrdFuel_Order_Qty)
OR odf.OrdFuel_Deliv_Net_Qty <= (300 - odf.OrdFuel_Order_Qty)

If I leave off the OR clause the query will return the rows as expected, but when I add the OR clause, the query just returns all rows in the joined table. Any help would be appreciated.

CodePudding user response：

You've got a few things wrong with that query:

1. You have the less/greater than backwards
2. You have the subtraction backwards
3. You need an and, not an or

What you are probably looking for is:

SELECT DISTINCT
    o.Ord_No,
    odf.OrdFuel_Order_Qty,
    odf.OrdFuel_Deliv_Net_Qty
FROM Order_Details_Fuel odf
JOIN Orders o ON odf.OrdFuel_Ord_Key = o.Ord_Key
WHERE odf.OrdFuel_Deliv_Net_Qty <= (300   odf.OrdFuel_Order_Qty)
and odf.OrdFuel_Deliv_Net_Qty >= (odf.OrdFuel_Order_Qty - 300)

As pointed out by JNevill, this could even be simplified to:

ABS(odf.OrdFuel_Deliv_Net_Qty - odf.OrdFuel_Order_Qty) <= 300

Full credit to him on that one.