Mail traffic accounting

[ispcomm]

Kilburn,

try something like this:

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 = '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 = '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 = 'alias_forward'

This will give you a table if recipients including subdomains, a reference main domain and a mail_forward field (That you need to parse for commas to find out how many recipients are there).

Hope that helps.

ispcomm

EDIT: Typo in the sql query. Fixed.
EDIT2: Full query including domain aliases.