zuckerreports- help creating where query accross multiple modules

[ChaseWalker]

I've created a module called payments.

Contacts has a one to many relationship to it.
There is a field called payments.firstrenewaldate

In ZuckerReports, I have "Contacts" selected as my listing module but in my "Where clause/query" I have:

contacts.anniversary_date_c BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) AND
payments.firstrenewaldate NOT NULL

The first part works on it's own. We use fields in the payments module (firstrenewaldate, secondrenewaldate, etc) to track when renewal notices are sent out.

I'm basically trying to generate a report that says "Give me everyone whose anniversary date (custom field we set) is coming in the next 30 days AND who has not already received their first renewal notice."

My problem is that I know how to reference all the fields in the Contacts, but I don't know how to reference fields from other tables in my where clause/query in Zucker.

I paid $50 for the Zucker manual (only to find it for free on google and I've read it thoroughly. I've read an iReports manual that does not say much about clause formatting. Been searching forums all day.

Would appreciate anyone's help if they can point me to some example clauses I can dissect.

[ChaseWalker]

By the way, this is how we ended up doing it:

contacts_cstm.anniversary_date_c <= CURDATE()
AND contacts_cstm.anniversary_date_c >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)
AND
contacts.id IN (select target_id from campaign_log LEFT OUTER JOIN campaigns ON campaign_log.campaign_id = campaigns.id WHERE campaigns.name LIKE '1st%' AND campaigns.name NOT LIKE '%2nd%' AND campaign_log.activity_date >= DATE_ADD(CURDATE(), INTERVAL - 6 MONTH))

This goes in ZuckerReports under "Report and Query Templates" edit the report and put this query in the "where" clause.

What it's doing is saying select people who:
Have anniversary date custom field past in the last 5 days.
AND
who have a campaign log entry in the last 6 months where the campaign is named "1st%" but no existence of a campaign log entry in last 6 months where campaign is named "2nd%"

This is the logic we use to select people who have received their first renewal notice via the campaign/mail merge features. We mark them as sent and look at their campaign log history when sending the second set of renewal notices because we only want to select and mail members who have already received the first notice.

This is a good example of the exact format you need to query across multiple modules in Zucker in the where clause/query. We found that an extra ) was needed on the end of the query because of their SQL construction format.