Sub Select Error / Help
Is it possible to build a query with a sub select inside a left join in the query builder? I wrote the following sql by hand but when I try to open the recordset in the bindings window to drag in the fields I get an error.
SELECT clients.clientID, clients.clientName, SUM(LastWeek.taskHrs)
FROM clients LEFT JOIN (SELECT tasks.taskID, tasks.taskClient, tasks.taskHrs, tasks.taskDue FROM tasks
WHERE WEEKOFYEAR(tasks.taskDue)=WEEKOFYEAR(NOW() - INTERVAL 1 WEEK)) AS LastWeek ON clients.clientID = LastWeek.taskClient
GROUP BY clients.clientID
The error I get when I try to expand the recordset is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
What's really funny is if I double click the recordset and click the Test button, it returns without an error.
Perhaps the sql will execute fine, it's just not formatted exactly as server behaviors would like it to be?