Recently I wanted to reduce the size of my main manitou-mail database, and thus I’ve decided to delete all the messages I’ve received from some mailing-lists. I know these messages are archived elsewhere, so that I could re-import them if needed anyway.

But I didn’t like the idea of deleting also the messages that I’ve sent to these mailing-lists, because it would have broken the rule I’ve adopted of keeping all sent messages. Also on second thought, I thought it would be best to include also the whole threads in which I’ve participated, so that the context of the messages would still be available (BTW, the entire thread to which a message belongs can be recalled in the user interface by the contextual menu command: “Show thread on new page”).

So the question was, how to select all the messages tagged with certain tags, but excluding all threads for which at least one message has the Sent status? As usual, the database and the SQL come to help. First I looked up the tag_id’s of the tags corresponding to the mailing-lists, let’s say they were 3,6 and 10. And then I just expressed in SQL the sentence above. The result is:

SELECT mt.mail_id FROM mail_tags mt JOIN mail m1 USING (mail_id)
 WHERE tag IN (3,6,10) AND NOT EXISTS
 (SELECT 1 FROM mail m2 WHERE m2.thread_id=m1.thread_id AND m2.status&128!=)

After issuing this query with the selection dialog, with the Limit To field empty to ensure that all messages are retrieved, all that was needed to accomplish the task was to select all the messages in the resulting list (Ctrl-A) and hit the Del key.