MSAccess-排除加入w/通配符? [英] MSAccess - Exclusion Join w/Wildcard?

问题描述

我取得了很好的成功( MSAccess-使用表作为条件进行查询)当标题"与包含在包含"表中的子字符串匹配时，使用以下方法加入联系人"表:

I had good success (MSAccess - Query using a Table as Criteria) joining a Contacts table when Title matches a substring contained in an Include table using:

SELECT Contacts.Title
FROM Contacts 
INNER JOIN Include ON Contacts.Title like '*' & Include.String & '*';

我认为我现在需要更进一步，使用排除表来排除一些标题.示例:上面的方法正确地抓住了"CEO"，但是也抓住了"CEO Admin Assistant"，这是我不想要的.

I think I now need to go further and Exclude some titles, using an Exclude table. Examples: The above approach properly grabbed the "CEO", but also grabbed the "CEO Admin Assistant", which I do not want.

以下返回所有行；猜测，因为其使用的是.AND.不是.OR.比较排除"列表中的条目.

The following is returning all rows; guessing because its using an .AND. not an .OR. comparing the entries in my Exclude list.

SELECT Contacts.Title
FROM Contacts 
INNER JOIN Exclude ON Contacts.Title not like '*' & Exclude.String & '*';

任何建议如何使用表格完成所需的排除?

Any suggestions how to accomplish the desired Exclusion using a table?

推荐答案

在联接条件中使用inner join和not like会产生一对多关系，因为对于联系人"中的每一行，排除"中可能有很多条记录，其中联系人"记录是 不喜欢 ，而只有一个记录它的 .

Using an inner join with not like in the join criteria will yield a one-to-many relationship, since, for each row in Contacts, there are likely to be many records in Exclude which the Contacts record is not like and only one record that it is like.

相反，如果您想继续使用计算出的联接条件(而不是将not in或not exists与子查询一起使用)，我建议在排除表上使用left join并使用is null条件在where子句中仅选择在Exclude中没有匹配记录的那些记录，例如:

Instead, if you wanted to continue using the calculated join criteria (as opposed to using not in or not exists with a subquery), I would suggest using a left join on the Exclude table and an is null condition within the where clause to select only those records which don't have a matching record in Exclude, e.g.:

select contacts.title 
from contacts left join exclude on contacts.title like '*' & exclude.string & '*'
where exclude.string is null

这篇关于MSAccess-排除加入w/通配符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！