asked on

Many NOT LIKE conditions in a Totals Query

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

Kyle Abrahams, PMP

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Davisro

ASKER

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));

Gustav Brock

OK.
This was the keywords:

Select KeyWord From tblKeywords

Open in new window