User Tools

Site Tools


adv_sample_user_queries_plus

This is an old revision of the document!


Advanced sample SQL queries

Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag:

SELECT m.mail_id FROM mail m
JOIN mail_addresses ma ON (ma.mail_id=m.mail_id)
JOIN addresses USING(addr_id)
WHERE status&256<>0
AND email_addr LIKE '%@example.org'
AND NOT EXISTS
  (SELECT 1 FROM mail_tags mt JOIN (SELECT tag_id FROM tags WHERE name='Sample Tag') t ON (t.tag_id=mt.tag) WHERE mt.mail_id=m.mail_id)
;

See the database schema documentation for definitions of tables and columns.

adv_sample_user_queries_plus.1407150843.txt.gz · Last modified: 2014/08/04 11:14 by daniel