CODEWITHSUNDEEP
mysqldatabase
Elbo Shindi Pangestu
Elbo Shindi Pangestu

Reputation: 2371

How to write safe SQL query on strict SQL Mode

I write query like this:

SELECT name, class, period FROM subject 
WHERE organization IN (?,?,?,?,?,?,?,?) 
GROUP BY class, period

But, it causing error like this:

Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bsi.subject.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I found solving from this. But, I am not so sure it safe. I am database junior. Is it safe? There is another solution without changing the sql mode setting?

I also found this solution. If column name not necessary, just simply remove it. But, I consider to add it to reuseable code.

Upvotes: 0

Views: 292

Answers (1)

nbk
nbk

Reputation: 49375

Every column in the Select statements has to be in the GROUP BY or have aN Aggregation function

like

SELECT GROUP_CONCAT( DISTINCT   name) As name, class, period 
FROM subject 
WHERE organization IN (?,?,?,?,?,?,?,?) 
GROUP BY class, period

this would add all names from one period and class as a comma seperated strntg

Upvotes: 1

Related Questions