CODEWITHSUNDEEP
sqlsql-serveroperators
amart47
amart47

Reputation: 95

AND/OR operator not filtering as desired

I'm trying pull a list of trucks that are marked as 'Inventory' (status = 'I') that do not have a 'FiWipStatusCode' of 'P', 'B', or 'F'.

Put differently, I want to pull a list of trucks marked as Inventory that DO have a 'fiwipstatuscode' of 'null' or 'C'.

The code below isn't filtering the way I want it to. thanks!

SELECT     InventoryVehicle.StockNo, InventoryVehicle.Status, VehicleSales.FiWipStatusCode
FROM       InventoryVehicle
INNER JOIN VehicleSales ON InventoryVehicle.StockNo = VehicleSales.StockNo
WHERE      (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'F') AND (InventoryVehicle.Balance > '15000') OR
           (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'P') AND (InventoryVehicle.Balance > 15000) OR
           (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'B') AND (InventoryVehicle.Balance > 15000) OR
           (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode IS NULL) AND (InventoryVehicle.Balance > 15000) OR
           (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode = 'C') AND (InventoryVehicle.Balance > 15000)

Upvotes: 2

Views: 71

Answers (2)

Aubrey Love
Aubrey Love

Reputation: 1034

A lot of people get these two operators mixed up. In real life we say, “bring me all the pants AND shirts” and we expect to receive all of both items.

SQL sees the request differently and will return only the pants-with-shirts as well as the shirts-with-pants, but will not return pants only or shirts only.

In SQL you need say “bring me all the pants OR shirts” if you want all of both items.

Here are couple of "good rules of thumb" to follow.

1) The AND operator displays a record ONLY if ALL the conditions on both sides of the AND statement are true.

2) The OR operator displays a record if ANY of the conditions on both sides of the AND statement are true.

This is where JOIN statements come in handy.

Hope this helps.

Upvotes: 1

squillman
squillman

Reputation: 13641

You're mixing up precedene of AND and OR. I think you probably want this:

SELECT InventoryVehicle.StockNo, InventoryVehicle.Status, VehicleSales.FiWipStatusCode
FROM InventoryVehicle INNER JOIN VehicleSales ON InventoryVehicle.StockNo = VehicleSales.StockNo
WHERE ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'F') AND (InventoryVehicle.Balance > '15000')) OR
                  ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'P') AND (InventoryVehicle.Balance > 15000)) OR
                  ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'B') AND (InventoryVehicle.Balance > 15000)) OR
                  ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode is NULL) AND (InventoryVehicle.Balance > 15000)) OR
                  ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode = 'C') AND (InventoryVehicle.Balance > 15000));

Note the () encapsulating each set of AND checks.

And since each group of AND's is now encapsulated you can make it even easier:

SELECT InventoryVehicle.StockNo, InventoryVehicle.Status, VehicleSales.FiWipStatusCode
FROM InventoryVehicle INNER JOIN VehicleSales ON InventoryVehicle.StockNo = VehicleSales.StockNo
WHERE (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode <> 'F' AND InventoryVehicle.Balance > '15000') OR
      (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode <> 'P' AND InventoryVehicle.Balance > 15000) OR
      (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode <> 'B' AND InventoryVehicle.Balance > 15000) OR
      (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode is NULL AND InventoryVehicle.Balance > 15000) OR
      (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode = 'C' AND InventoryVehicle.Balance > 15000);

And, since two of the filter parameters are the same in each case you can simplify it even further:

SELECT InventoryVehicle.StockNo, InventoryVehicle.Status, VehicleSales.FiWipStatusCode
FROM InventoryVehicle INNER JOIN VehicleSales ON InventoryVehicle.StockNo = VehicleSales.StockNo
WHERE InventoryVehicle.Status = 'I'
AND InventoryVehicle.Balance > '15000'
AND (
    VehicleSales.FiWipStatusCode <> 'F' OR
    VehicleSales.FiWipStatusCode <> 'P' OR
    VehicleSales.FiWipStatusCode <> 'B' OR
    VehicleSales.FiWipStatusCode is NULL OR
    VehicleSales.FiWipStatusCode = 'C'
);

Upvotes: 2

Related Questions