Inner select query help

mRender

I have tried everything, but I can not get this query to function with my editor. The following code is the query in SQL, because everything I've tried with the editor does not work. Is this idea possible?

SELECT projects.projectid, clients.clientname, projects.projectname, projects.lowest, projects.highest, timeworked

FROM projects

JOIN ( SELECT userprojectid, SUM(timeworked) AS timeworked

FROM time

GROUP BY userprojectid ) AS timeworked ON timeworked.userprojectid = projects.projectid

JOIN clients ON clients.clientid = projects.clientid 

JOIN userprojects ON userprojects.projectid = projects.projectid 

ORDER BY projects.projectid

allan

Hi,

Are you trying to use that with the Editor PHP or .NET libraries? Unfortunately at this time they don't support nested queries like that, or the group by clause. They aren't likely to in the near future either as it would add a major about of complexity to the libraries.

You can certainly execute a query like that using the sql() method of the Database instance, but the Editor wouldn't be able to automatically update the date on submit - that would require some custom code I'm afraid.

Regards,
Allan

mRender

Ah, right. Sorry for not being specific enough. The SQL method of PHP would be enough for my particular page, I didn't expect the Editor to be able to handle data given this way.

Good to know that it's possible.

Thanks for your time.