Mysql IF IN GROUP_CONCAT breaks

Question

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.

score 12 · Accepted Answer · answered Aug 31 '11 at 15:18

12

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

answered Aug 31 '11 at 15:18

ajreal

46,720
11
89
119

Thanks, never thought about using SUM. I used it like this `IF(sum( if(service.service_code='network', 1, 0) ) > 0 ,'Yes','No) as networkservice` and it works great. – Cleric Sep 01 '11 at 07:13

score 9 · Answer 2 · edited Feb 04 '16 at 13:15

9

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

edited Feb 04 '16 at 13:15

Francesco Casula

26,184
15
132
131

answered Feb 03 '16 at 14:20

duromir

91
1
2

i highly appreciate this in Having clause (in my case). ;) – ralphcuneta Sep 30 '20 at 07:03

score 1 · Answer 3 · answered Aug 31 '11 at 15:10

1

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.

answered Aug 31 '11 at 15:10

cypher

6,822
4
31
48

`if( 'network' IN ( GROUP_CONCAT( DISTINCT services.service_code SEPARATOR '\',\' ' ) ), 'Ja','Nej')` should return a list that looks like `GROUP_CONCAT('1', '2', '3', '4')`, feels that way anyway. But it doesn't work. Omitting the group_concat only works if 'network' is the service_code present on the row that Im grouping to, and I cant do Order By on service_code. Unquote networks breaks the sql. And I dont have the possibility to use a SubQuery. Thanks for your response. – Cleric Sep 01 '11 at 07:22

score 0 · Answer 4 · edited Sep 15 '15 at 04:24

0

Will using the below code help?

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

edited Sep 15 '15 at 04:24

Ormoz

2,975
10
35
50

answered Sep 15 '15 at 03:23

Raghav

1

Mysql IF IN GROUP_CONCAT breaks

4 Answers4