I have to perform a select on a table in the following manner: I am searching for some strings in 2 columns, the search string can be in either one or the second, or in both.
To select from a single column I would use something like:
SELECT * FROM table
WHERE TITLE LIKE '%pat1%' OR TITLE LIKE '%pat2%' OR TITLE LIKE '%pat3%' etc.
There is no limit for the search patterns.
But I want to perform this query so that it checks if the search patterns are in either a column or the other.
Concatenate the two columns to become one.
where CONCAT(title,subject) like '%pat1%'
also check out
MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION] )
In the DBMS I work with mainly, I would create a temporary table to hold the N pattern strings, and then use that in a cross join with the main table to do the query against multiple patterns and multiple columns:
CREATE TEMP TABLE SearchPatterns(Pattern VARCHAR(32));
INSERT INTO SearchPatterns('%patt1%');
INSERT INTO SearchPatterns('%path2%');
INSERT INTO SearchPatterns('%pith3%');
INSERT INTO SearchPatterns('%sith4%');
SELECT DISTINCT T.*
FROM MainTable AS T CROSS JOIN SearchPatterns AS P
WHERE T.Col1 LIKE P.Pattern
OR T.Col2 LIKE P.Pattern;
This extends to an arbitrary number of patterns and an arbitrary number of columns, of course. What I cannot tell you is whether MySQL is flexible enough to allow you to do this.
Can't you just OR both columns?
SELECT * FROM table WHERE
col1 LIKE '%path1%' OR col2 LIKE '%path1%' OR
col1 LIKE '%path2%' OR col2 LIKE '%path2%' OR
etc.
Or depending on the exact semantics of your search:
SELECT * FROM table WHERE
( col1 LIKE '%path1%' OR col1 LIKE '%path2%' OR etc.) OR
( col2 LIKE '%path1%' OR col2 LIKE '%path2%' OR etc.)
