SQL query puzzle

Classic Servoy
1

Two tables, “Company” and “CR”. Each Company record may or may not have one or more related records in CR. Or none.

I need a SQL query which finds all companies whose most recent related CR record is before or after a particular date. The operative phrase here is “most recent CR”. I want to test just the most recent related CR, ignoring all others.

The following code appears to test the first related CR found, not the newest one.

var days = 7;
var maxReturnedRows = 1000;
var d1 = new Date();
d1.setDate(d1.getDate() - days); // reduce today to the threshold date

var query = "SELECT company.company_id FROM company WHERE ";
query += "(company.cr_status = 0 OR company.cr_status IS NULL) ";
query += "AND (SELECT cr.crid FROM cr WHERE cr.creation_date <? ";
query += "AND company.company_id = cr.comid ) > 0 ";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[d1],maxReturnedRows);
controller.loadRecords(dataset);

I’ve played around with adding max(cr.creation_date) and max(cr.crid) to the Join. The max operator appears to test each CR record in turn, doesn’t go directly to the most recent.

I’ve attempted to sort the CR table so the above code will test the first record but have been unable to figure out where to put the code to perform that operation.

ORDER BY cr.creation_date desc
```Can anyone spot what's needed?
2

Hello Morley,

you need to look at the HAVING clause here.

In your case, something like this should work:

SELECT company.company_id
FROM company, cr 
WHERE company.company_id = cr.comid  // and some other criteria
GROUP BY company.company_id
HAVING (MAX(cr.creation_date) < yourdate)

Try that in your database directly first and then implement it in your method.

Hope this helps.