SQL Query Help

4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 6, 2018   (RSS)

By Dave - March 5, 2018

Hi testplan, 

I don't see anything immediately obvious about why that query wouldn't be working.

I'd try executing the subqueries individually and then adding and removing lines to isolate the exact issue.

For example, does this run on it's own: 

                  SELECT MAX(recent_date)
                  FROM (SELECT * FROM `cmsb_my_table`) AS mt2
                  WHERE mt2.account = mt.account AND mt2.year = mt.year
                  GROUP BY account, `year`

And if so, does the containing query run if you replace the sub-query with the output of the subquery?

Also, if you don't already have it, this free plugin lets you run MySQL queries straight from the CMSB plugin menu:
https://www.interactivetools.com/add-ons/developer-console/

Hope that helps point you in the right direction.

Dave Edis - Senior Developer

interactivetools.com

By (Deleted User) - March 6, 2018

Thanks Dave,

I figured out the problem. When using aliases for tables with a delete, you have to specify the aliased table for deletion. So my code before should have started like this:

DELETE mt FROM

Thanks for the plugin, that looks to be useful!

By Dave - March 6, 2018

Ok, great.  Glad to hear you got it working! Thanks for sharing the resolution.  Cheers.

Dave Edis - Senior Developer

interactivetools.com