How to use GROUP_CONCAT functionality without GROUP BY in MySQL?

Question

This is the original SQL statement to retrieve course data:

SELECT
    `courses`.`id` AS `id`,
    `courses`.`title` AS `title`,
    `courses`.`description` AS `description`,
    MATCH (coursedata.title) AGAINST ('Salsa') * 5 + MATCH (coursedata.description) AGAINST ('Salsa') * 2 AS `relevance`,
    GROUP_CONCAT(trainers.name SEPARATOR '|||') AS `trainers`
FROM `courses`
INNER JOIN `coursedata` ON `courses`.`id` = `coursedata`.`id`
        INNER JOIN `courses_trainers` ON `courses`.`id` = `courses_trainers`.`course_id`
INNER JOIN `trainers` ON `trainer_id` = `trainers`.`id`
GROUP BY `courses`.`id`
HAVING `relevance` >= '3'

Since it uses the MySQL Extensions to GROUP BY and cannot be executed, if the sql_mode is set to ONLY_FULL_GROUP_BY, I had to make it standard SQL conform:

SELECT * FROM (
    SELECT
        `courses`.`id` AS `id`,
        `courses`.`title` AS `title`,
        `courses`.`description` AS `description`,
        MATCH (coursedata.title) AGAINST ('Salsa') * 5 + MATCH (coursedata.description) AGAINST ('Salsa') * 2 AS `relevance`
        -- , GROUP_CONCAT(trainers.name SEPARATOR '|||') AS `trainers`
    FROM `courses`
    INNER JOIN `coursedata` ON `courses`.`id` = `coursedata`.`id`
    LEFT JOIN `courses_trainers` ON `courses`.`id` = `courses_trainers`.`course_id`
    LEFT JOIN `trainers` ON `trainer_id` = `trainers`.`id`
) AS subselect
WHERE `relevance` >= '3'

This one wors fine, but I cannot use the GROUP_CONCAT function (in order to concatenate multiple trainer fields to one field/string) anymore.

Error Code: 1064
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 'GROUP_CONCAT(trainers.name SEPARATOR '|||') AS `trainers`
    FROM `courses`
    INNER' at line 7

Is there another way to use the GROUP_CONCAT functionality?

I guess I don't understand the question. You need group context in order to use GROUP_CONCAT, would would you be concatenating if you weren't grouping by the course. And why would you just not group by all the fields other than trainers if this no need to operate in ONLY_FULL_GROUP_BY mode. — Mike Brant, Apr 16 '13 at 00:21
I cannot use the first statement, since `relevance` is an aggregated column. And when I use the second one with `GROUP BY` (`GROUP BY subselect.id, subselect.title, subselect.description`), I also get an error: `Error Code: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause`. — automatix, Apr 16 '13 at 00:51
Use your **1st** query and list **all non-aggregated columns** in the `GROUP BY` clause. This way you will not rely on extensions and wouldn't care about the settings. — PM 77-1, Apr 16 '13 at 01:19
You mean `SELECT ... FROM ... JOIN .. JOIN ... GROUP BY courses.id, courses.id, courses.title, courses.description, coursedata.title, coursedata.description HAVING relevance >= '3'`? It does not work in `ONLY_FULL_GROUP_BY` `sql_mode`: `Error Code: 1463 Non-grouping field 'relevance' is used in HAVING clause` — automatix, Apr 16 '13 at 08:35
Solved : [Group_Concat() in SQL: Solved][1] Please Check this post. [1]: http://stackoverflow.com/a/20262372/3045124 — Novy, Nov 28 '13 at 09:42

How to use GROUP_CONCAT functionality without GROUP BY in MySQL?

0 Answers0