-- 'joomla-staging.a.menutype' isn't in GROUP BY SQL=
SELECT `a`.`id`,`a`.`menutype`,`a`.`title`,`a`.`alias`,`a`.`note`,`a`.`path`,`a`.`link`,`a`.`type`,`a`.`parent_id`,`a`.`level`,`a`.`published` AS `a.published`,`a`.`component_id`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`browserNav`,`a`.`access`,`a`.`img`,`a`.`template_style_id`,`a`.`params`,`a`.`lft`,`a`.`rgt`,`a`.`home`,`a`.`language`,`a`.`client_id`,CASE WHEN a.type = 'component' THEN a.published+2*(e.enabled-1) WHEN a.type = 'url'AND a.published != -2 THEN a.published+2 WHEN a.type = 'url'AND a.published = -2 THEN a.published-1 WHEN a.type = 'alias'AND a.published != -2 THEN a.published+4 WHEN a.type = 'alias'AND a.published = -2 THEN a.published-1 WHEN a.type = 'separator'AND a.published != -2 THEN a.published+6 WHEN a.type = 'separator'AND a.published = -2 THEN a.published-1 WHEN a.type = 'heading'AND a.published != -2 THEN a.published+8 WHEN a.type = 'heading'AND a.published = -2 THEN a.published-1 END AS published ,l.title AS language_title, l.image AS language_image, l.sef AS language_sef,u.name AS editor,c.element AS componentname,ag.title AS access_level,`mt`.`id` AS `menutype_id`,`mt`.`title` AS `menutype_title`,COUNT(asso2.id)>1 as association,e.name AS name
FROM `#__menu` AS a
LEFT JOIN `#__languages` AS l ON l.lang_code = a.language
LEFT JOIN `#__users` AS u ON u.id = a.checked_out
LEFT JOIN `#__extensions` AS c ON c.extension_id = a.component_id
LEFT JOIN #__viewlevels AS ag ON ag.id = a.access
LEFT JOIN `#__menu_types` AS `mt` ON `mt`.`menutype` = `a`.`menutype`
LEFT JOIN #__associations AS asso ON asso.id = a.id AND asso.context='com_menus.item'
LEFT JOIN #__associations AS asso2 ON asso2.key = asso.key
LEFT JOIN #__extensions AS e ON e.extension_id = a.component_id WHERE a.id > 1 AND a.client_id = 0 AND (a.published IN (0, 1)) AND a.menutype IN('mainmenu','mainmenu-en-gb','mainmenu-fr-fr','mainmenu-pt-pt')
GROUP BY a.id, e.enabled, l.title, l.image, u.name, c.element, ag.title, e.name, mt.id, mt.title
ORDER BY a.lft ASC
LIMIT 20

-- 'joomla-staging.a.menutype' isn't in GROUP BY SQL=
SELECT `a`.`id`,`a`.`menutype`,`a`.`title`,`a`.`alias`,`a`.`note`,`a`.`path`,`a`.`link`,`a`.`type`,`a`.`parent_id`,`a`.`level`,`a`.`published` AS `a.published`,`a`.`component_id`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`browserNav`,`a`.`access`,`a`.`img`,`a`.`template_style_id`,`a`.`params`,`a`.`lft`,`a`.`rgt`,`a`.`home`,`a`.`language`,`a`.`client_id`,CASE WHEN a.type = 'component' THEN a.published+2*(e.enabled-1) WHEN a.type = 'url'AND a.published != -2 THEN a.published+2 WHEN a.type = 'url'AND a.published = -2 THEN a.published-1 WHEN a.type = 'alias'AND a.published != -2 THEN a.published+4 WHEN a.type = 'alias'AND a.published = -2 THEN a.published-1 WHEN a.type = 'separator'AND a.published != -2 THEN a.published+6 WHEN a.type = 'separator'AND a.published = -2 THEN a.published-1 WHEN a.type = 'heading'AND a.published != -2 THEN a.published+8 WHEN a.type = 'heading'AND a.published = -2 THEN a.published-1 END AS published ,l.title AS language_title, l.image AS language_image, l.sef AS language_sef,u.name AS editor,c.element AS componentname,ag.title AS access_level,`mt`.`id` AS `menutype_id`,`mt`.`title` AS `menutype_title`,COUNT(asso2.id)>1 as association,e.name AS name
FROM `#__menu` AS a
LEFT JOIN `#__languages` AS l ON l.lang_code = a.language
LEFT JOIN `#__users` AS u ON u.id = a.checked_out 
LEFT JOIN `#__extensions` AS c ON c.extension_id = a.component_id
LEFT JOIN #__viewlevels AS ag ON ag.id = a.access
LEFT JOIN `#__menu_types` AS `mt` ON `mt`.`menutype` = `a`.`menutype`
LEFT JOIN #__associations AS asso ON asso.id = a.id AND asso.context='com_menus.item'
LEFT JOIN #__associations AS asso2 ON asso2.key = asso.key
LEFT JOIN #__extensions AS e ON e.extension_id = a.component_id
WHERE a.id > 1 AND a.client_id = 0 AND (a.published IN (0, 1)) AND a.menutype IN('mainmenu','mainmenu-en-gb','mainmenu-fr-fr','mainmenu-pt-pt')
GROUP BY a.id, e.enabled, l.title, l.image, u.name, c.element, ag.title, e.name, mt.id, mt.title
ORDER BY a.lft ASC