Many NOT LIKE conditions in a Totals Query

Davisro
Davisro used Ask the Experts™
on
Experts,

In the following Sql, if I have two NOT LIKE conditions in the HAVING clause.
SELECT [CONCUR 2018_01-04].TripNumber, [CONCUR 2018_01-04].BusinessPurpose
FROM [CONCUR 2018_01-04]
GROUP BY [CONCUR 2018_01-04].TripNumber, [CONCUR 2018_01-04].BusinessPurpose
HAVING ((([CONCUR 2018_01-04].BusinessPurpose) Not Like "*Party*" And ([CONCUR 2018_01-04].BusinessPurpose) Not Like "*Flowers*"));

Open in new window

1.  If I have 40 or 50 NOT LIKE conditions, is there a better way to write the query than writing so many expressions in the HAVING clause
2. Is there a way to manage the NOT LIKE keywords in another table (that users can add to the list on a form) and have the keywords feed into the NOT LIKE conditions in the query?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer

Commented:
from:
https://stackoverflow.com/questions/4420554/use-like-with-field-values-in-mysql


You can do a Concat on a table: CONCAT('*',REPLACE(field,'*','\*'),'*')

in order to do this you would create a new table, say ValidBusinessPurposes
This could have 2 columns, tripnumber ValidBusinessPurpose

I then flipped your logic to look for a match for a valid business purpose, and if a match is found, then exclude it from the result.
SELECT [CONCUR 2018_01-04].TripNumber, [CONCUR 2018_01-04].BusinessPurpose
FROM [CONCUR 2018_01-04] 
left join [ValidBusinessPurpose] as vbp on  [CONCUR 2018_01-04].TripNumber = vbp.TripNumber and  [CONCUR 2018_01-04].BusinessPurpose  like CONCAT('*',REPLACE(vbp.ValidBusinessPurpose,'*','\*'),'*') 
where 
-- you're looking for something without a valid business purpose.
vbp.ValidBusinessPurpose is null 
GROUP BY [CONCUR 2018_01-04].TripNumber, [CONCUR 2018_01-04].BusinessPurpose

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Yes, use a table holding the keywords.
I told you today how to handle this:

https://www.experts-exchange.com/questions/29115047/Multiple-Not-Like-statements-in-query.html#a42662358
DavisroBudget Analyst

Author

Commented:
Gustav, yes you did. but I didn't understand something...in your example
Select *
From YourTable
Where RTrim(Left([Description], Instr([Description], " "))) Not In (Select KeyWord From tblKeywords)
I didn't understand how to get the keywords from my table into your Not In clause. Is this a subquery?

What did work for me is to create an Excluded Records table
SELECT DISTINCT tblConcurTrips.TripNumber
FROM tblExclusionWords, tblConcurTrips
WHERE (((InStr([tblConcurTrips].[BusinessPurpose],[tblExclusionWords].[Keywords]))>0));


And then do a Not Match query to remove the Excluded Records and it works
SELECT CONCUR.TripNumber, CONCUR.BusinessPurpose
FROM CONCUR LEFT JOIN qTripsExclude ON CONCUR.TripNumber = qTripsExclude.TripNumber
GROUP BY CONCUR.TripNumber, CONCUR.BusinessPurpose, qTripsExclude.TripNumber
HAVING (((qTripsExclude.TripNumber) Is Null));
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
OK.
This was the keywords:

Select KeyWord From tblKeywords

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial