CODEWITHSUNDEEP
mysqlsqlgroup-by
Valentin BEAULE
Valentin BEAULE

Reputation: 404

sql_mode=only_full_group_by but no over riding of the option

I'm running this SQL statement:

SELECT 
    *, GROUP_CONCAT(contact) AS contact 
FROM  
    table4, table3, table2, table1 
WHERE 
    table1.id = 1 
    AND table2.var_id = table1.id 
    AND table3.var_id = table1.id 
GROUP BY 
    table1.id

Getting an error:

GROUP_CONCAT(contact)" doit récupérer les contact de la table4

It works only because I used this:

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

My problem is that my request must be used on different server so I need something more stable that don't just avoid the problem.

How can I retrieve the same results but without over riding the option?

Expression #%u of %s is not in GROUP BY clause and contains nonaggregated column '%s' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The error repeats itself for each table, and if I add the asked elements, my result has no concatenated contacts but only the first "contact" result.

Upvotes: 0

Views: 387

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You can restructure your query in such a way that selecting all columns won't cause the full mode error. Something like this:

SELECT *
FROM table1 t1
INNER JOIN table2 t2
    ON t1.id = t2.var_id
LEFT JOIN
(
    SELECT var_id, GROUP_CONCAT(contact) AS contact
    FROM table3
    GROUP BY var_id
) t3
    ON t2.var_id = t3.var_id

By doing the aggregation in a subquery involving only that table, we get around the problem. I didn't stick with the query you showed us, because it is not clear, but I suspect your actual query varies even from that. In any case, hopefully this gives you an idea of what you can try.

Upvotes: 1

Related Questions