Retrieve good records from a bad record table

[Verticon::]

I have a situation where I need a table if bad items to match to. For
example, The main table may be as:

Table Main:
fd_Id INT IDENTITY (1, 1)
fd_Type VARCHAR(100)

Table Matcher:
fd_SubType VARCHAR(20)

Table Main might have a records like:
1 | "This is some full amount of text"
2 | "Here is half amount of text"
3 | "Some more with a catch word"

Table Matcher:
"full"
"catch"

I need to only get the records from the main table that do not have
anything in the match table. This should return only record 2.

[Erland Sommarskog]

Verticon:: (mi***@miben.net) writes:

I have a situation where I need a table if bad items to match to. For
example, The main table may be as:

Table Main:
fd_Id INT IDENTITY (1, 1)
fd_Type VARCHAR(100)

Table Matcher:
fd_SubType VARCHAR(20)

Table Main might have a records like:
1 | "This is some full amount of text"
2 | "Here is half amount of text"
3 | "Some more with a catch word"

Table Matcher:
"full"
"catch"

I need to only get the records from the main table that do not have
anything in the match table. This should return only record 2.

SELECT mn.fd_id, mn.fd_Type
FROM tablemain mn
WHERE NOT EXISTS (SELECT *
FROM tablematcher mt
WHERE md.fd_Type LIKE '%' + mt.fd_SubType + '%')
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx