Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

› WebAssist Forums › MySQLi Server Behaviors › Query Builder › Sub Select Error / Help

View all replies to this thread

Sub Select Error / Help

Thread began 3/29/2016 7:36 am by Kevin | Last modified 3/29/2016 10:03 am by Kevin | 437 views | 1 replies

3/29/2016 7:36 amKevin

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?

Thanks,
Kevin

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now. Build a store, a gallery, or a web-based email solution.