类似于“不喜欢”（从其他表中选择语句） [英] something like NOT LIKE (select statement from other table)

问题描述

我有3张桌子

SUFFIX_LIST(Item VARCHAR),
PREFIX_LIST(Item VARCHAR), and
VALID_LIST(Part1 VARCHAR, Part2 VARCHAR)

现在我需要列VALID_LIST.Part1& VALID_LIST.Part2与
Part1与SUFFIX_LIST中的任何项目都不部分匹配，而
Part2与PREFIX_LIST中的任何项目不部分匹配。

now i need column VALID_LIST.Part1 & VALID_LIST.Part2 in way that Part1 doesnt match partially from any of the Item from SUFFIX_LIST and Part2 doesnt match partially from any of the Item from PREFIX_LIST.

我正在使用SQLite作为本地数据库，并使用c＃进行应用程序开发！

I am using, SQLite as my local database and using c# for application development!

预先感谢！

有效列表

sample1, somedomain.com
samplebanword, someotherdomain.com
simplestring, someotherotherdomain.net
morestring, my-domain.com

PREFIX_LIST

PREFIX_LIST

ban
text

SUFFIX_LIST

SUFFIX_LIST

.net
otherother

现在，我要的是，当查询必须得到时

now, what i want is, when the query i must get

Output
--------
sample1, somedomain.com
morestring, my-domain.com

推荐答案

这是一个解决方案n可以使用您提供的其他信息起作用：

Here's a solution that works using the additional information you've supplied:

SELECT * FROM VALID_LIST WHERE NOT EXISTS 
  (SELECT * FROM PREFIX_LIST WHERE VALID_LIST.Part1 LIKE '%' || Item || '%')
  AND NOT EXISTS
  (SELECT * FROM SUFFIX_LIST WHERE VALID_LIST.Part2 LIKE '%' || Item || '%')

您可以在此处查看其运行情况： http://sqlfiddle.com/#!7/1da46/3/0

You can see it in operation here: http://sqlfiddle.com/#!7/1da46/3/0

这篇关于类似于“不喜欢”（从其他表中选择语句）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！