Mail traffic accounting

[kilburn]

I had to slightly edit your query as a mail address can be both mailbox and forwarded at the same time so it's mail_type is ie 'normal_mail,normal_forward':

Code:

SELECT concat( mail_acc, '@', domain_name ) AS mail_from, domain_name, mail_forward
FROM mail_users
LEFT JOIN domain
USING ( domain_id )
WHERE mail_type LIKE '%normal_forward'
UNION
SELECT concat( mail_acc, '@', subdomain_name, '.', domain_name ) AS mail_from, domain_name, mail_forward
FROM mail_users
LEFT JOIN domain
USING ( domain_id )
LEFT JOIN subdomain
USING ( domain_id )
WHERE mail_type LIKE '%subdom_forward'
UNION
SELECT concat( mail_acc, '@', alias_name ) AS mail_from, domain_name, mail_forward
FROM mail_users
LEFT JOIN domain
USING ( domain_id )
LEFT JOIN domain_aliasses
USING ( domain_id )
WHERE mail_type LIKE '%alias_forward'

Working on the fix right now