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

Latest outgoing message per distinct recipient (to which a message was ever addressed):

SELECT mail_id FROM (SELECT DISTINCT ON (m1.addr_id) m.mail_id,m1.addr_id
 FROM mail_addresses m1 JOIN mail m USING(mail_id)
  WHERE m1.addr_type=2 AND m.status&129=129
  ORDER BY m1.addr_id,m.msg_date DESC) s

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

adv_sample_user_queries_plus.1409441033.txt.gz · Last modified: 2014/08/31 01:23 by daniel