JOIN Help!

[admin]

Hi All,

I have a JOIN statement:

SELECT d.*, b.invoice_id FROM domain_info d LEFT JOIN billing_info b ON d.domain_id=b.domain_id WHERE billing_cycle = '12' OR billing_cycle = 'Z' OR billing_cycle = 'C' GROUP BY domain_name

In addition to the fields this statement returns, I would also like it to include the result of this second statement:

SELECT sum(ammount_due) FROM billing_info WHERE domain_id = $domain_id AND status = 0

How do I do this?

I store my data in two tables:

domain_info
+---------------------+--------------+
domain_id | int(11) |
domain_name | varchar(70) |
login | varchar(8) |
password | varchar(8) |
first_name | varchar(128) |
last_name | varchar(128) |
company_name | varchar(128) |
address1 | varchar(128) |
address2 | varchar(128) |
city | varchar(128) |
state | char(2) |
zip | varchar(10) |
phone | varchar(12) |
email | varchar(128) |
billing_cycle | char(2) |
date_opened | timestamp(6) |
setup_fee | float(10,2) |
monthly_fee | float(10,2) |
service_description | text |
+---------------------+--------------+

billing_info
+-------------+--------------+|
invoice_id | int(11) | |
domain_id | int(11) |
ammount_due | float(10,2) |
date_due | timestamp(6) |
status | int(1) |
+-------------+--------------+

How can I augment my SELECT statement to also return this additional sum(ammount_due) field for each domain (row)?

Help!

Nick