Hi,

I have some left outer joins. The data within the fields is always of varchar or integer type. Querying the data will never send back NULL-values, because the default value of strings is set to an empty string. But in my outer joins there appear NULL-values.

The queries have to support a Pervasive DB and my MySQL DB. The Pervasive DB groups NULL values and empty strings to one group, but MySQL doesn't. The result looks like this:

MySQL DB:
+----------+--------+-------+------+---------+--------+
| count(*) | midkey | datum | zeit | midnnam | reskey |
+----------+--------+-------+------+---------+--------+
| 411 | [NULL] | | | [NULL] | [NULL] |
| 2 | [NULL] | | | [NULL] | |
......

Pervasive DB:
+----------+--------+-------+------+---------+--------+
| count(*) | midkey | datum | zeit | midnnam | reskey |
+----------+--------+-------+------+---------+--------+
| 413 | | | | | |
........

So the reskey is grouped by Pervasive to an empty string but not by MySQL. There is a NULL-group and an empty string group.

Does anyone know a possiblity to set NULL values to empty strings in MySQL GROUP BY queries?

Thanks for your help,
Markus

use COALESCE(fieldname,'')

Hi,

I'm wondering that COALESCE is accepted in the GROUP BY clause - but it seems to work.

Thank you for your help,
Markus