On Dec 13, 2010, at 11:56 AM, Zhang Huangbin wrote:
> I try to fetch all mails sent from users under domain "example.com" with
> below SQL command:
>
> # <--
> SELECT
> msgs.sid, msgs.mail_id,
> sender.email as sender_email,
> recip.email as recipient
> FROM msgs
> LEFT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
> LEFT JOIN maddr AS sender ON (msgs.sid = sender.id AND sender.domain in
> ('com.example'))
> LEFT JOIN maddr AS recip ON (msgrcpt.rid = recip.id AND recip.domain NOT in
> ('com.example'))
> WHERE content IS NOT NULL
> ORDER BY msgs.time_num DESC
> LIMIT 10
> OFFSET 0;
> # <--
Use 'RIGHT JOIN' instead of 'LEFT JOIN' fixes 'sender_email=NULL' and
'recipient=NULL'. :)
--
Zhang Huangbin
- Open Source Mail Server Solution for Red Hat(R) Enterprise Linux,
CentOS, Debian, Ubuntu, OpenSuSE, FreeBSD: http://www.iredmail.org/
------------------------------------------------------------------------------
Oracle to DB2 Conversion Guide: Learn learn about native support for PL/SQL,
new data types, scalar functions, improved concurrency, built-in packages,
OCI, SQL*Plus, data movement tools, best practices and more.
http://p.sf.net/sfu/oracle-sfdev2dev
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
Please visit http://www.ijs.si/software/amavisd/ regularly
For administrativa requests please send email to rainer at openantivirus dot
org
