How to use GROUP_CONCAT in Stored procedure MySQL

Question

I'm trying to use comma separated values (@job_skills) in another query like as follows in my stored procedure. But it does not work as expected. Amy be it only consider first value from comma separated string. Is there any other way to patch up ?

SELECT GROUP_CONCAT(skillId) as job_skills FROM tbljob_skill as jskl WHERE jskl.jobpostingId = param_jobid INTO @job_skills;

    BLOCK6: BEGIN
        DECLARE curl_job12 CURSOR FOR SELECT * FROM (SELECT js.jobseekerId FROM tbljobseeker_skill as jss INNER JOIN tbmstjobseeker as js ON (js.jobseekerId = jss.jobseekerId) INNER JOIN tblrecommended_jobseekers_details as rjd ON (js.jobseekerId=rjd.jobseekerId) WHERE jss.skillId IN (@job_skills) AND js.isActive = 'Y' AND js.lat<>0 AND js.lang<>0 AND rjd.sessionid=param_sessionid GROUP BY js.jobseekerId) as m;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET job_done12 = TRUE;
        OPEN curl_job12;
            read_loop_job12: LOOP
                FETCH curl_job12 INTO jsid, miles;
                IF job_done12 THEN
                    LEAVE read_loop_job12;
                END IF;
                INSERT INTO temp_skills (jobId,skill,jobseekerId) VALUES (param_sessionid,@job_skills,jsid);
                IF jsid <> '' THEN
                    SELECT COUNT(1) INTO check_exists FROM tblrecommended_jobseekers_details WHERE sessionid = param_sessionid AND type = 'match 1 - SKILLS' AND jobseekerId = jsid;
                    IF check_exists = 0 THEN
                        START TRANSACTION;
                        INSERT INTO temp_jobseekers_scores (jobseekerId,score,type,miles) VALUES (jsid,score_skills,'match 1',miles);
                        INSERT INTO tblrecommended_jobseekers_details (jobseekerId,score,type,miles,sessionid) VALUES (jsid,score_skills,'match 1 - SKILLS',miles,param_sessionid);
                        COMMIT;
                    END IF;
                END IF;
            END LOOP;
        CLOSE curl_job12;
    END BLOCK6;

Answer 1

Generated comma separated values are a SET and treated as a single string when used with IN function. Instead you have to use FIND_IN_SET to compare with other values.

Change your WHERE clause in CURSOR definition as below.

DECLARE curl_job12 CURSOR FOR 
        SELECT * FROM (
            SELECT js.jobseekerId 
              FROM tbljobseeker_skill as jss 
             INNER JOIN tbmstjobseeker as js 
                     ON (js.jobseekerId = jss.jobseekerId) 
             INNER JOIN tblrecommended_jobseekers_details as rjd 
                     ON (js.jobseekerId=rjd.jobseekerId) 
             --  WHERE jss.skillId IN (@job_skills) 
             WHERE FIND_IN_SET( jss.skillId, @job_skills ) 
               AND js.isActive = 'Y' 
               AND js.lat<>0 
               AND js.lang<>0 
               AND rjd.sessionid=param_sessionid 
             GROUP BY js.jobseekerId
        ) as m;

Refer to Documentation:

• FIND_IN_SET(str,strlist)
  • Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings