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]

Re: Retrieve good records from a bad record table

Verticon:: (miben@miben.ne t) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx