Advanced MySQL Query ISSUE

[axiom82]

I have this mysql query:

 

	SELECT
COUNT(DISTINCT IF(p.registered >= CURDATE() AND p.registered < DATE_ADD(CURDATE(), INTERVAL 1 DAY), p.id, NULL)) AS publishers,
COUNT(DISTINCT IF(a.registered >= CURDATE() AND a.registered < DATE_ADD(CURDATE(), INTERVAL 1 DAY), a.id, NULL)) AS advertisers,
SUM(IF(acs.`date`=CURDATE(), acs.viewed, NULL)) AS viewed,
SUM(IF(acs.`date`=CURDATE(), acs.emailed, NULL)) AS emailed,
SUM(IF(acs.`date`=CURDATE(), acs.printed, NULL)) AS printed,
COUNT(DISTINCT IF(ac.created >= CURDATE() AND ac.created < DATE_ADD(CURDATE(), INTERVAL 1 DAY), ac.id, NULL)) AS coupons_total,
COUNT(DISTINCT IF(ac.created >= CURDATE() AND ac.created < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND ac.`type`=7, ac.id, NULL)) AS coupons_7day,
COUNT(DISTINCT IF(ac.created >= CURDATE() AND ac.created < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND ac.`type`=14, ac.id, NULL)) AS coupons_14day,
COUNT(DISTINCT IF(ac.created >= CURDATE() AND ac.created < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND ac.`type`=30, ac.id, NULL)) AS coupons_30day,
earnings.total,
earnings.payout,
earnings.net
FROM publisher p
LEFT JOIN (publisher_advertiser pa) ON (pa.publisher_id=p.id)
LEFT JOIN (advertiser a) ON (a.id=pa.advertiser_id)
LEFT JOIN (advertiser_coupon ac) ON (ac.advertiser_id=a.id AND ac.transaction_id != 0)
LEFT JOIN (advertiser_coupon_stats acs) ON (acs.advertiser_coupon_id=ac.id)
LEFT JOIN (

	SELECT advertiser_id,
	SUM(IF(created >= CURDATE() AND created < DATE_ADD(CURDATE(), INTERVAL 1 DAY), amount - tax, NULL)) AS total,
	SUM(IF(created >= CURDATE() AND created < DATE_ADD(CURDATE(), INTERVAL 1 DAY), (amount - fee) - tax, NULL)) AS payout,
	SUM(IF(created >= CURDATE() AND created < DATE_ADD(CURDATE(), INTERVAL 1 DAY), fee, NULL)) AS net
	FROM advertiser_coupon
	WHERE transaction_id != 0

) earnings ON (earnings.advertiser_id=ac.advertiser_id)

 

You can see it isn't a beginner query.  In fact, it joins 5 tables.  One of these joins is a subquery selecting sums.

 

The problem is with this portion of the query:

	LEFT JOIN (

	SELECT advertiser_id,
	SUM(IF(created >= CURDATE() AND created < DATE_ADD(CURDATE(), INTERVAL 1 DAY), amount - tax, NULL)) AS total,
	SUM(IF(created >= CURDATE() AND created < DATE_ADD(CURDATE(), INTERVAL 1 DAY), (amount - fee) - tax, NULL)) AS payout,
	SUM(IF(created >= CURDATE() AND created < DATE_ADD(CURDATE(), INTERVAL 1 DAY), fee, NULL)) AS net
	FROM advertiser_coupon
	WHERE transaction_id != 0

) earnings ON (earnings.advertiser_id=ac.advertiser_id)

 

When there are no coupon records, there is NOTHING to join `earnings` with `ac` (a.k.a. `advertiser_coupon`).  Which means the join is basically ON NULL=NULL.

 

Is there a workaround for this where the REST of the query will still retrieve values even with the earnings table returning NULL?

 

Because it is SUMMING earnings for each advertiser_coupon record...for accurate results it has to be in a sub query...unless that's not true?

 

Please help!

[Gamic]

Left OUTER Join (Each row from the table one the "left" of the query will be joined with 0, 1 or more rows from the table on the "right"). If you have no records in the subselect, then it would still retrieve that fact.