SQL WHERE子句从一个字段中搜索多个参数

Question

基本上，我希望MS Access查询输出符合以下所有三个标准的所有记录:

1. Expanded_Status = "合格但未注册">
2. 医院= "UHN">
3. 评论= "transplant"或";tx"或";post-transplant"

注释栏有三个不同版本的单词"移植";有些字段可能有"tx"而不是"移植"有些记录可能有"移植后"。而不是"移植">

下面的查询由于某种原因输出了不符合所有三个条件的记录。

请帮我修改查询，以便我只能找到满足所有三个条件的记录

SELECT [First Name], [Last Name], [Subject ID], Expanded_Status, Hospital, Comments
FROM [Barriers UHN Screen - 2017 Mailing]
WHERE Expanded_Status = "Eligible but not enrolled" AND Hospital = "UHN"  AND Comments LIKE "transplant"  OR Comments OR "post transplant" OR Comments = "tx";

Answer 1

基于@Harun24HR的回答:

SELECT 
[First Name], [Last Name], [Subject ID], Expanded_Status, Hospital, Comments
FROM 
[Barriers UHN Screen - 2017 Mailing]
WHERE 
Expanded_Status = "Eligible but not enrolled" AND Hospital = "UHN"  AND 
(LOWER(Comments) LIKE "*transplant*"  OR LOWER(Comments) LIKE "*post transplant*" OR LOWER(Comments) LIKE "*tx*");

我加了两件事:

1. 在Comments上调用小写确保您只比较小写字符串-如果有人有"移植后";在评论中，它可能不匹配"移植后">
2. 通配符*在比较字符串的开始和结束-这告诉Access我的比较字符串可以在评论中的任何地方。

像操作符

Answer 2

试试这个

SELECT 
[First Name], [Last Name], [Subject ID], Expanded_Status, Hospital, Comments
FROM 
[Barriers UHN Screen - 2017 Mailing]
WHERE 
Expanded_Status = "Eligible but not enrolled" AND Hospital = "UHN"  AND 
(Comments = "transplant"  OR Comments = "post transplant" OR Comments = "tx");

将where子句的Comment部分括在括号中，告诉access将每个部分作为AND子句的一部分分别求值。