[SQL] How can I exclude rows where the description contains a phrase, but only if it is...

I have to create a dataset of our email logs to find responses to our prize competition. Our email log is very poor, so I am limited in how I can identify records we are interested in.

We only want it to be included if it has the word 'prizedraw' in it. However, we don't want it to be included if the record has the phrase 'you have been entered in our prizedraw'...unless there is another occurrence of the word 'prizedraw' in the email chain.

I'm struggling to come up with a viable solution for this, has anyone got any ideas?

An example of what I'm hoping to keep in the dataset is where the email log is someone responding to the email saying 'you have been entered in our prizedraw' saying 'I didn't enter this prizedraw'. This would all be in one email chain so would all be on one row in the emaildescription column.

Tried using where emaildescription like '%prizedraw%'and not like '%you have been entered in our prizedraw%' but this excludes email chains where a second appearance of prizedraw appears

Continue reading...