SQL - Search multiple columns

Question

I'm trying to search multiple columns for similar/matching values, while ensuring the "UID" is unique.

This seemed simple, but the following query ignores the "UID" and will return anything that matches under "SalesRef, "CustomerPO" or "Ref".

Any ideas why this would be?

SELECT * FROM OrderHeader
         INNER JOIN DespatchDetails ON DespatchDetails.Ref = OrderHeader.Ref
         INNER JOIN OrderStatus ON OrderStatus.Ref = OrderHeader.Ref
         WHERE OrderHeader.UID = '$uid'
         AND OrderStatus.SalesRef LIKE '%$search%'
         OR OrderStatus.CustomerPO LIKE '%$search%'
         OR OrderStatus.Ref LIKE '%$search%'
         ORDER BY OrderHeader.OrderDate DESC";

ekochergin · Accepted Answer · 2021-03-18 10:42:35Z

There is a problem in the WHERE condition of yours. Consider to use brackets every time when you're using OR in WHERE part of the query

Let's have a closer look to your version

...
WHERE OrderHeader.UID = '$uid'
AND OrderStatus.SalesRef LIKE '%$search%'
OR OrderStatus.CustomerPO LIKE '%$search%'
...

To the database it says: get me all the lines where

WHERE OrderHeader.UID = '$uid'
AND OrderStatus.SalesRef LIKE '%$search%'

or

OrderStatus.CustomerPO LIKE '%$search%'

it means when this "OrderStatus.CustomerPO LIKE '%$search%'" condition is true, it will return a row without checking for the UID column and so on.

As far as I can understand the logic, all you need is to update the WHERE part of the query with the following

     WHERE OrderHeader.UID = '$uid'
     AND (OrderStatus.SalesRef LIKE '%$search%'
     OR OrderStatus.CustomerPO LIKE '%$search%'
     OR OrderStatus.Ref LIKE '%$search%')
     ORDER BY OrderHeader.OrderDate DESC";

Collectives™ on Stack Overflow

SQL - Search multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related