Retrieve good records from a bad record table

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.Verticon:: (miben@.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx