Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to NOT match on an exception list Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 12:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:36 AM
Points: 118, Visits: 567
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'


Post #1439968
Posted Monday, April 8, 2013 12:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439972
Posted Monday, April 8, 2013 12:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439978
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse