Doug Coats Doug Coats - 2 months ago 10
MySQL Question

Multiple WHERE, LIKE conditions

Is this query valid? Or do I need to break it down more?

SELECT SUM(colA) as 'colA',
SUM(colB) as 'colB'
FROM tblName
WHERE colA,colB REGEXP 'Fail'
OR colA, colB='Pass'
ORDER BY colA, colB;


Reason I ask is I have a large amount of columns to look up and I am trying to discover the most effective way of doing this without writing this long drawn out query.

Any advice would be greatly appreciated.

Answer

For =/equality testing, there's the IN notation:

WHERE x=1 or x=2 or x=3 ....
WHERE x='pass' or y='pass' or ....

can be a simple

WHERE x IN (1,2,3,....)
WHERE 'pass' IN (x,y,....)

You MAY be able to do something similar with regex matches, e.g.

WHERE 'Fail' REGEXP CONCAT(colA, colB, etc...)

but that depends on exactly what your regex pattern is. For anything else, there's no shortcuts. You have to write it all out.