How to NOT match on an exception list

Question

Post reply

How to NOT match on an exception list

SQLWannabe

SSCrazy

Points: 2842
More actions
April 8, 2013 at 12:10 pm

#272790

If I have a table of Products that have a ProductName and I have a table of ProductNames that I don't want the first table matched to, what is the best process in which to do this? The names will be LIKE each other, but will not match exactly. When I pull data, I only want the ones from dbo.Products where they don't look like the Products from dbo.Exceptions. In my example, I want to pull back Products that don't look like WidgetD or WidgetE.
Normally I would write:
SELECT ProductDesc FROM dbo.Products WHERE ProductDesc NOT LIKE '%WidgetD%' AND ProductDesc NOT LIKE '%WidgetE%'
The query should return WidgetA, WidgetB, and WidgetC (3 records)
However, I don't want to jack with the code every time a new exception is added.
Is there a better method? (example code below)
CREATE TABLE [dbo].[Products](
[ProductDesc] [varchar](200) NULL
)
INSERT INTO Products(ProductDesc) SELECT 'WidgetA'
INSERT INTO Products(ProductDesc) SELECT 'WidgetB'
INSERT INTO Products(ProductDesc) SELECT 'WidgetC'
INSERT INTO Products(ProductDesc) SELECT 'WidgetD'
INSERT INTO Products(ProductDesc) SELECT 'WidgetE'
CREATE TABLE [dbo].[Exceptions](
[ProductDesc] [varchar](200) NULL
)
INSERT INTO Exceptions(ProductDesc) SELECT 'Big WidgetD'
INSERT INTO Exceptions(ProductDesc) SELECT 'Small WidgetE'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

fantastic job providing the DDL and sample data!

I think in this case, you need to match partial strings, right?

you will want to join the tables together, but use a LIKE statement for the join criteria,(where you might be used to A.ID = B.ID

this seems to do what i think we are after:

SELECT Products.*

FROM Products

LEFT OUTER JOIN Exceptions

ON Exceptions.ProductDesc LIKE '%' + Products.ProductDesc + '%'

WHERE Exceptions.ProductDesc IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Something like this:

CREATE TABLE [dbo].[Products](

[ProductDesc] [varchar](200) NULL

)

INSERT INTO Products(ProductDesc) SELECT 'WidgetA'

INSERT INTO Products(ProductDesc) SELECT 'WidgetB'

INSERT INTO Products(ProductDesc) SELECT 'WidgetC'

INSERT INTO Products(ProductDesc) SELECT 'WidgetD'

INSERT INTO Products(ProductDesc) SELECT 'WidgetE'

CREATE TABLE [dbo].[Exceptions](

[ProductDesc] [varchar](200) NULL

)

INSERT INTO Exceptions(ProductDesc) SELECT 'Big WidgetD'

INSERT INTO Exceptions(ProductDesc) SELECT 'Small WidgetE'

GO

select

*

from

[dbo].[Products] p

where

not exists(select 1 from [dbo].[Exceptions] e where e.ProductDesc like '%' + p.ProductDesc + '%');

GO

drop table [dbo].[Products];

drop table [dbo].[Exceptions];

GO