Web lists-archives.com

Why does a group_concat on a join change aggregate values?

Fighting a bit with this one...

If I do something like (pseudo):

SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types FROM tbl1

returns something like:

n  c_types
1  t9

when I add a left join though:

SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types,
GROUP_CONCAT(two.types) AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON
tbl1.id = tbl2.id

returns something like:

val  c_types          d_types
3    t9,t9,t9         a2,a3,a9

I can have as many group_concats against the same table with varying
results and they don't affect COUNT() but once I do that JOIN things start
to fall apart.

What is happening behind the scenes?


Paul Halliday