Should have showed the whole thing. Take a look here (click image to see full output):
http://www.pintumbler.org/tmp On Thu, May 7, 2015 at 4:11 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > Hi Paul, > > On 5/7/2015 10:17 AM, Paul Halliday wrote: > >> 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? >> >> Thanks! >> >> Here's a simple test. Change the query to no longer have the aggregate > functions, then start counting rows by hand. > > SELECT > val AS n > , types AS c_types > , two.types AS d_types > FROM tbl1 > LEFT JOIN tbl2 AS two > ON tbl1.id = tbl2.id > > The other thing that springs to mind is that you lack a GROUP BY in your > query. It isn't required but they can often help get you to the correct > answer. > > Best regards, > > -- > Shawn Green > MySQL Senior Principal Technical Support Engineer > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > Office: Blountville, TN > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > > -- Paul Halliday http://www.pintumbler.org/
