loadRecords(query) with a JOIN?

Classic Servoy
1

Hi, I’m getting errors when I try and do a loadRecords() on a query that requires an INNER JOIN. I can’t do it with a loadDataset() because of the number of records involved.

Is it possible to do this?

var query = 'SELECT jobs.jobid FROM jobs ’
query += ’ INNER JOIN jobcommissions ON jobs.jobid = jobcommissions.jobid ’
query += 'WHERE jobs.jobcreationdate IS NOT NULL ’
query += ’ ORDER BY jobs.jobid DESC ’

controller.loadRecords(query);

2

No. Currently that is not possible. If you look at the query that Servoy fires you see that everything starting with “Join” is removed. If you (and I) are lucky, this limitation is removed one day.

In your case however it works, because you use an inner join. You just need to write your query as:

var query = 'SELECT jobs.jobid FROM jobs, jobcommissions  '
query += ' WHERE jobs.jobid = jobcommissions.jobid '
query += 'AND jobs.jobcreationdate IS NOT NULL '
query += ' ORDER BY jobs.jobid DESC '

But you can not use LEFT or RIGHT joins.

3

thanks!