0

Imagine table with two columns (there are more, but not relevant anyhow to this question):

  `EAN` bigint(20) unsigned DEFAULT NULL,
  `parameter_id` mediumint(8) unsigned DEFAULT NULL,

This query does not work and returns error:

SELECT EAN FROM eTovar WHERE EAN GROUP BY EAN HAVING parameter_id IS NULL

Error in query (1054): Unknown column 'parameter_id' in 'having clause'

But this query works:

SELECT EAN FROM eTovar WHERE EAN GROUP BY EAN HAVING MIN(parameter_id) != MAX(parameter_id)

I assume that for some reason, HAVING is able to reach MIN(parameter_id) but unable to reach clean parameter_id. Why?

I have MariaDB 5.5 (should be same as MySQL 5.5).

Martin
  • 21
  • 6

1 Answers1

2

According to MySQL 5.5 docs

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the HAVING clause that are not named in the GROUP BY clause. A MySQL extension permits references to such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate. [...]
In some cases, you can use MIN() and MAX() to obtain a specific column value even if it is not unique.

Have you tried replacing HAVING with WHERE in your first SQL:

SELECT EAN FROM eTovar WHERE EAN AND parameter_id IS NULL GROUP BY EAN;
ChipsLetten
  • 2,923
  • 1
  • 11
  • 28
  • I was going to suggest the exact same thing. Using WHERE instead of HAVING works for me with similarly constructed queries using MySQL Workbench on an InnoDB table. – Evan Bechtol Jun 26 '15 at 13:13
  • If I put `parameter_id IS NULL` into `WHERE`, it will be filtered out in `HAVING` – Martin Jun 26 '15 at 13:22
  • I guess I just have to put `parameter_id` when selecting columns and than remove column later... – Martin Jun 26 '15 at 13:23
  • Shouldn't that be `SELECT EAN FROM eTovar WHERE EAN AND parameter_id IS NULL GROUP BY EAN` ? – Simon Forsberg Jun 26 '15 at 13:46
  • @SimonAndréForsberg - yes, indeed. – ChipsLetten Jun 26 '15 at 14:05
  • @Martin are you saying you need to use `HAVING` for some reason? Is `parameter_id` a field or a property of the aggregation? [Having vs Where](http://stackoverflow.com/a/9253267/4243498) – ChipsLetten Jun 26 '15 at 14:12
  • I want to get list of `EAN` which have `EAN` same as some other record while they do not have same `parameter_id` + or `parameter_id` is `NULL`. – Martin Jun 26 '15 at 14:30
  • I want to group records with `parameter_id`. Two similar product have same `parameter_id`. I am joining products together when they have same `EAN` but they are not yet joined using `parameter_id`. There is more different reasons why products have same `parameter_id`, `EAN` is one of them. – Martin Jun 26 '15 at 14:36
  • @ChipsLetten I was visualising `HAVING` only as "another `WHERE`", thanks for [Having vs Where]. That explains why it can not see `parameter_id`. – Martin Jun 26 '15 at 14:41