Geek Answers Handbook

Mysql IF IN GROUP_CONCAT breaks

I am doing fairly large SQL, so I apologize that can not provides a larger 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 may have an agreement, and the agreement may have many services. I am trying to find out if the “network” is one of the services in this agreement.

Since GROUP_CONCAT returns a comma separated list, it feels perfect for my business. But I can't get it to work, and my ideas are running out.

If there is only one service, and this service is a "network", it returns "yes", but if there is more than one, then "No".

(INT) service_id, , INT Im look for . INT, "network" , No.

:

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


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

, .

.

+5
4

group_concat, group_concat

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

GROUP_CONCAT , FIND_IN_SET. -.

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

GROUP_CONCAT , group_concat :

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

:

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

group_concat: ( network)

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

, -select:

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

although it will be much slower.

+1
source

Will the hint below be used?

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

More articles:


All Articles