I could try to explain the WHERE EXISTS clause, but this link will do it more justice:
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html I thought that query would work off the top of my head, but forgot that EXISTS ignores the field list in the sub-select statement, so it didn't do what I originally thought. Try this code to see if it gets you the records you're looking for. I tested this one and it seemed to work, if I understand what you want.
This one groups the records by totals_query, then gets the smallest PK value out of each group, thus returning the equivalent of the DISTINCT statement.
See below:
- Code: Select all
// This query will get the records you want in your dataset
var query = "SELECT DISTINCT totals_query FROM donation_detail WHERE detail_date = ?"
// This query will return the PK's of the records you're wanting
// which will pass to .loadRecords() below
var query_pk = "select pk from donation_detail where pk in " +
"(SELECT min(pk) FROM donation_detail WHERE detail_date = ? group by totals_query)"
var args = new Array()
args[0] = utils.dateFormat(donation_date, 'MM/dd/yyyy')
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, -1);
var ds_pk = databaseManager.getDataSetByQuery(controller.getServerName(), query_pk, args, -1);
var recCount = dataset.getMaxRowIndex()
// Load the desired records into a form's foundset using the PK's from ds_pk
forms.Enter_Donation_Detail.controller.loadRecords(ds_pk)
// put some code here to do what you want with dataset
Let me know if it works okay.