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
