"And" "Or" Search Criteria

  • Thread starter Thread starter tpmcanally03
  • Start date Start date
T

tpmcanally03

I have a field that contains summaries of articles. I want to search
within the field for the following:
"Red" and "Blue" or "Green" and Blue"
I want my results to return all articles that contain both "red" and
"blue" in an article and all articles that contain both "green" and
"blue" in an article. I can easily return all articles that contain
"red" or "green" but the "AND" function is causing me to return ZERO
articles which I know is incorrect.
Thanks for all of your help in advance.
 
In your example all the articles to be returned will contain 'blue' and will
also contain either 'red' or 'green' (or possibly both 'red' and 'green'),
So, assuming you are using the LIKE operator to match the patterns the logic
is:

WHERE summary LIKE "*blue*" AND (Summary LIKE "*red*" OR summary LIKE
"*green*)

By enclosing the Boolean OR operation in parentheses this evaluates
independently, so if the parenthesised expression evaluates to TRUE (the
article contains 'red' or 'green') AND the article also contains 'blue' the
complete expression will evaluate to TRUE and the row will be returned.

The expression above is the same logically as:

WHERE (summary LIKE "*blue*" AND Summary LIKE "*red*") OR (summary LIKE
"*blue*" AND summary LIKE "*green*)

which is closer to your plain English expression – "Red" and "Blue" or
"Green" and Blue"; the difference is the use of the parentheses in the formal
logical expression, which are omitted in the plain English equivalent.

Ken Sheridan
Stafford, England
 
I have a field that contains summaries of articles. I want to search
within the field for the following:
"Red" and "Blue" or "Green" and Blue"
I want my results to return all articles that contain both "red" and
"blue" in an article and all articles that contain both "green" and
"blue" in an article. I can easily return all articles that contain
"red" or "green" but the "AND" function is causing me to return ZERO
articles which I know is incorrect.
Thanks for all of your help in advance.
Click to expand...

tpmcanally,

SELECT <your columns>
FROM <your table> AS YT1
WHERE (YT1.ArticleSummary LIKE "*Red*"
AND YT1.ArticleSummary LIKE "*Blue*")
OR (YT1.ArticleSummary LIKE "*Green*"
AND YT1.ArticleSummary LIKE "*Blue*")

(That's aircode, but the logic should be right <crosses fingers>.)


Sincerely,

Chris O.
 
Oops, missed a quotes character:

WHERE summary LIKE "*blue*" AND (summary LIKE "*red*" OR summary LIKE
"*green*")

Ken Sheridan
Stafford, England
 
Oops, missed a quotes character:

WHERE summary LIKE "*blue*" AND (summary LIKE "*red*" OR summary LIKE
"*green*")

Ken Sheridan
Stafford, England





- Show quoted text -
Click to expand...

Thanks everyone. Adding the word 'Like' and the "*xxxxx*" worked
perfectly.
 
Back
Top