sql where not like from other table

I have 2 tables. I table with alot of data/columns and 1 table with 1 column exclusions.
i need a select statement that will show tableA where columnA is NOT LIKE '%tableB.text%"
AND tableA.columnB is NOT LIKE '%tableB.text%'

The closest I got to work is this which works as an exact match. How can I adjust this to show like with %% on both sides
select * from errorlogs
where errorlogs.filename not in
(select exclusion.text from exclusion)
and errorlogs.ip not in
(select exclusion.text from exclusion)

Open in new window

rivkamakAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT el.*
FROM dbo.errorlogs el
WHERE
    NOT EXISTS(
        SELECT 1
        FROM dbo.exclusion ex
        WHERE
            el.filename LIKE '%' + ex.text + '%' OR
            el.ip LIKE '%' + ex.text + '%'
    )
0
 
Brian CroweDatabase AdministratorCommented:
There may be a better way to do this with a function and CROSS APPLY but...

Also I'm assuming you have a unique identifier of some kind in errorlogs which I am referring to as ErrorLogID

SELECT errorlogs.*
FROM errorlogs
WHERE ErrorLogID NOT IN (
SELECT DISTINCT errorlogs.ErrorLogID
FROM errorlogs
CROSS JOIN exclusion
WHERE errorlogs .filename LIKE '%' + exclusion.text + '%')
0
 
rivkamakAuthor Commented:
That didn't work. I didn't get anything back
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Brian CroweDatabase AdministratorCommented:
Please post your table creation scripts for errorlogs and exclusions so I can play with it.
0
 
Scott PletcherSenior DBACommented:
SELECT el.*
FROM dbo.errorlogs el
INNER JOIN dbo.exclusion ex ON
    el.ColumnA NOT LIKE '%' + ex.text + '%' AND
    el.columnB NOT LIKE '%' + ex.text + '%'
0
 
Brian CroweDatabase AdministratorCommented:
I don't think that will work because you will actually get duplicate errorlog records for each exclusion entry that doesn't match.  As I understand it each errorlog record needs to be compared against eachexclusion entry and only included in the resultset if there is NO match found.
0
 
Scott PletcherSenior DBACommented:
DOH, now that I see more of the q, you're probably right.  I went by only part of the original q.
0
 
rivkamakAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.