Search code examples
sqldatabasedatabase-administrationsql-like

How do I declare priority in SQL statements?

My question seems to be quite simple, but I'm worried the answer might actually be somewhat complex. I am trying to perform a simple Select query that behaves like the following.

Here is the code:

SELECT * FROM tbl_tbl WHERE tbl_tbl.colA LIKE '%foo%' OR tbl.tbl.colA LIKE '%oof%' AND 
   tbl_tbl.colB LIKE '%bar%' OR tbl_tbl.colB LIKE '%rab%'

So I am just searching for 4 strings (2 in each column), and if I find one in each pair, I want to show that entire entry.

Mathematically, it makes quite a bit of sense to me.

I want to do (This OR That) AND (One OR Another) where any combination of This/One, This/Another, etc. passes the expression.

Pretty simple right?

How do I tell SQL to work right (you know, like that obscure way in my mind)?

Currently, I'm getting entries out of my table where only 1 of the column disciplines match, and that's not giving me the specificity of the priority I am looking for.

Solution

  • You would express it using parentheses and boolean logic in the where clause:

    SELECT *
FROM tbl_tbl t
WHERE (t.colA LIKE '%foo%' OR t.colA LIKE '%oof%') AND 
      (t.colB LIKE '%bar%' OR t.colB LIKE '%bar%');

    Do note that this is based on your example in the question. The second clause of the AND has two conditions that are the same. I assume this is a typo in the question, but not knowing the right pattern, I've left it in the answer.