I'm working on a project now for a client, and database happen to be my weakness, and I have to query 2 tables to generate some reports.
I have one table which contains a date field called payoff_date which indicates the date of a loan being paid off. If it is not paid off, it has the default value '0000-00-00'
I have another table, which contains a field called last_payment which stores the date of a payment for a given loan. Since people can make multiple payments on one loan, there can be many entries for one loan.
In the report, I have a dropdown which can narrow the search to paid status of yes or no.
To specify a non-paid loan I use
WHERE finance_advances.payoff_date > CURDATE() OR finance_advances.payoff_date = '0000-00-00'
That seems to work fine.
I also want to allow them to choose a payment date greater or less than a certain number.
I need to be able to do this in one query so they can be saved and called on any date and have up to date results.
I guess I need to query the payments received table with something like
DATEDIFF(CURDATE(), payments_received.payment_date) > 30
The one problem with that is since there are multiple dates, it uses the oldest one, and also it doesnt correlate the payment date to the payoff date with the same loan id. it seems to just use the lowest one in the table that matches.
If someone might be able to forulate a basic query that could put the two together it'd help a lot.
Thanks everyone
