Mysql IF IN GROUP_CONCAT breaks

mysql group-concat
Cleric picture Cleric · Aug 31, 2011 · Viewed 11.2k times · Source

Im doing a fairly big SQL so I apologizes that cant provide a bigger example of my tables.

SELECT 
customer_id,
agreement_id,
if( 'network' IN ( GROUP_CONCAT( DISTINCT services.service_code
                                 SEPARATOR ',' )  ),
                  'Yes','No') as networkservice
FROM customers
INNER JOIN agreement USING(customer_id)
INNER JOIN services USING(agreement_id)
GROUP BY customer_id

A customer can have a agreement and a agreement can have a lot of services. What I'm trying to find out is if 'network' is one of the services in that agreement.

Since GROUP_CONCAT returns a comma separated list it feels perfect for my case. But I cant get it to work and I'm running out of ideas.

If there's only one service and that service is 'network' it returns yes, but if there's more then one it returns No.

If I use (INT)service_id instead it makes no difference, unless the INT Im looking for is first in the list. But thats only for INT, if 'network' is first in the list it returns No.

I've tried:

if( 'network' IN ( CAST(GROUP_CONCAT( DISTINCT services.service_code
                                      SEPARATOR ' ' ) AS CHAR)  ),
                   'Yes','No')

And

if( 'network' IN ( concat('\'',
                   GROUP_CONCAT(DISTINCT services.service_code
                                SEPARATOR '\', \'' ),
                   '\'') ), 'Yes','No')

I can provide more examples if my explanation sound confusing.

Thanks.

Answer

ajreal picture ajreal · Aug 31, 2011

I'm a big fan of group_concat, but you don't require group_concat in this case

sum( if(services.service_code='network', 1, 0) ) as networkservice

Related questions

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, fishing, coding The reason is that I'm selecting multiple values …

Read More
How to use GROUP_CONCAT in a CONCAT in MySQL

If I have a table with the following data in MySQL: id Name Value 1 A 4 1 A 5 1 B 8 2 C 9 how do I get it into the following format? id Column 1 A:4,5,B:8 2 C:9 I think I have to use GROUP_CONCAT. …

Read More
MySQL and GROUP_CONCAT() maximum length

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters. I'm very well aware that I can change the param group_concat_…

Read More