CODEWITHSUNDEEP
mysqlsql
N.F.
N.F.

Reputation: 4184

MySQL : search multiple columns with a single statement

I have a MySQL(5.7) table like this.

+--------+--------------+--------+-------+----------+--------+
| Field  | Type         | Null   | Key   | Default  | Extra  |
|--------+--------------+--------+-------+----------+--------|
| colA   | varchar(50)  | NO     | PRI   | <null>   |        |
| colB   | int(11)      | YES    |       | <null>   |        |
| colC   | int(11)      | YES    |       | <null>   |        |
| colD   | int(11)      | YES    |       | <null>   |        |
| colE   | int(11)      | YES    |       | <null>   |        |
| colF   | int(11)      | YES    |       | <null>   |        |
| colG   | int(11)      | YES    |       | <null>   |        |
| colH   | int(11)      | YES    |       | <null>   |        |
| colI   | int(11)      | YES    |       | <null>   |        |
| colJ   | int(11)      | YES    |       | <null>   |        |
| colK   | int(11)      | YES    |       | <null>   |        |
| colL   | int(11)      | YES    |       | <null>   |        |
| colM   | int(11)      | YES    |       | <null>   |        |

I would like to search all the colB, colC, ..., colM mod 100 is 1 or 2. I know I can do this with this.

SELECT * FROM MYTABLE WHERE 
(mod(colB, 100) = 1 OR mod(colB, 100) = 2) AND 
(mod(colC, 100) = 1 OR mod(colC, 100) = 2) AND 
(mod(colD, 100) = 1 OR mod(colD, 100) = 2) AND 
                 :
(mod(colM, 100) = 1 OR mod(colM, 100) = 2)

This SQL is very long and inefficient. Can I do this more smart?

Upvotes: 0

Views: 145

Answers (2)

zxcvc
zxcvc

Reputation: 355

You can do it like below:

SELECT * FROM MYTABLE WHERE 
where mod(colB, 100) in (1, 2) AND
colB = colC AND colB = colD AND colB = colE ... etc

Since you already check colB, as long as colB and other column is equals its means it should match with your first filter.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your query is pretty much the only way to do it. You can simplify it using in:

where mod(colB, 100) in (1, 2) AND
      mod(colC, 100) in (1, 2) AND
      . . .

More importantly, though, the need for this type of operation suggests that all the columns are representing a similar entity. That further suggests that you should be storing the values as one row per pair. Such a table would look something like this:

colA
colType
colValue

The query would be much simpler and shorter with this data structure.

Upvotes: 1

Related Questions