Mysql IF IN GROUP_CONCAT breaks

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 d开发者_开发知识库ifference, 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.

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

The GROUP_CONCAT function returns a string, so you can use FIND_IN_SET. It takes a comma separated string as a second argument.

IF(  FIND_IN_SET ('network', GROUP_CONCAT( DISTINCT services.service_code SEPARATOR ',' )  ), 'Yes','No') as networkservice

GROUP_CONCAT doesn't work like this, the query after group_concat looks like this:

GROUP_CONCAT('1,2,3,4') 

and for this to work, it would have to look like this:

GROUP_CONCAT('1', '2', '3', '4')

Instead, try ommitting the group_concat: (note the unquoted network)

if(network IN (DISTINCT services.service_code) ), 'Yes','No')

If this doesn't work, try it with a sub-select:

if(network IN (SELECT service_code FROM services WHERE ...)

although it will be much slower.

Will using the below code help?

IF((GROUP_CONCAT(Direct SEPARATOR ',' ) REGEXP 'Y' ), 'Yes','No') AS Direct