Im struggling with a junk mail filter. 3 tables

-repository holds subject text that is known junk.

-intermediate holds subject and message id

-message holds subject, body, message id.

I perform a find in table intermediate to delete all messages that match subjects with table repository. Once it deletes the messages in table intermediate it also deletes the corresponding messages based on message id in table message. The problem with this is that some subjects may say “Viagra now” so the match with the single word Viagra wont match and wont delete this message.

The solution I can think of is to use pattern count from table intermediate to repository to get rid of terms like Viagra, ect. I have attached the database so you can see what I mean. A script has been added to get rid of the junk messages but as you can see when it runs it does not delete the Viagra ones.

Im stumped on this one!

junkmail.fp7.zip

Why not just perform the same find in the message table?

That is true, I could make the same find from the message table. Let me explain, the subjects from the table repository are added as new junk mail comes in. I add a few new terms to this every day so that the next time a message comes in with this phrase it deletes it. This is why I need to keep these records the same, and have the table structure I have. If anyone can think of a better way to do this im all ears.

I don't see the need for the intermediate table either. Anyway, unless your phrases are single words, there is no (simple) way to do this with relationships. To classify a message as junk/not junk, you need to test the presence of each phrase, one by one, in the message's subject. This requires a custom function or a looping script.

You could also use a script to construct multiple Find requests based on your repository.

You wouldn't need an intermediate table for this or even a relationship between the repository and message tables.