Anwar Ahmad Anwar Ahmad - 1 year ago 49
MySQL Question

group_concat in mysql with "case when " conditions

I wrote sql code in mysql environment to concat the data . But, I couldn't get the correct result , and I am confusing about what is wrong with my sql code. my sql code is as follows:

SELECT case when cc.complex_check_id = cmt.comp_o_id then cc.status cstatus,sgk.status sgstatus,cc.NAME complex_check_name,cc.min min_flag,cmt.comp_t_name cmpt_name,group_concat(concat(concat(concat(concat(concat(f.NAME, ';') , sgk.NAME),' ') ,cc.operator),' '))
else cc.status cstatus,sgk.status sgstatus,cc.NAME complex_check_name,cc.min min_flag,'not' as cmpt_name,group_concat(concat(concat(concat(concat(concat(f.NAME, ';') , sgk.NAME),' ') ,cc.operator),' ')) end res_string
FROM complex_check_anag cc,lnksinglechecktocomplexcheck lk,single_check_anag sgk,functionalci f ,lnkconfigurationitemtosinglecheck lkcg,comp_t_anag cmt
WHERE cc.complex_check_id = lk.complex_check_id AND sgk.single_check_id = lk.single_check_id and f.id = lkcg.config_item_id
and sgk.single_check_id = lkcg.single_check_id and sgk.status = 'active' GROUP BY cc.NAME


could you give me some suggestions ,please ?...thanks a lot all of you !

Answer Source

The syntax you used for the CASE expression is not correct, you can only select one expression inside the case expression, but you selected more than one column, and I noticed that only one columns you need to select based on the case condition, so I moved all the columns out of the case expression except that column like this:

SELECT 
  cc.status cstatus, 
  sgk.status sgstatus,
  cc.NAME complex_check_name, 
  cc.min min_flag,
  group_concat(concat(concat(concat(concat(concat(f.NAME, ';') , sgk.NAME),' ') ,cc.operator),' ')),
  case when cc.complex_check_id = cmt.comp_o_id then cmt.comp_t_name 
                                                else 'not' as 
                                                end res_string 
FROM complex_check_anag cc ....
.... the rest of your query here

Also, you can rewrite your query using the INNER JOIN instead of the old join syntax like this:

SELECT 
  cc.status cstatus, 
  sgk.status sgstatus,
  cc.NAME complex_check_name, 
  cc.min min_flag,
  group_concat(concat(concat(concat(concat(concat(f.NAME, ';') , sgk.NAME),' ') ,cc.operator),' ')),
  case when cc.complex_check_id = cmt.comp_o_id then cmt.comp_t_name 
                                                else 'not' as 
                                                end res_string 
FROM complex_check_anag cc, comp_t_anag cmt
INNER JOIN lnksinglechecktocomplexcheck lk ON cc.complex_check_id = lk.complex_check_id
INNER JOIN functionalci f ON f.id = lkcg.config_item_id  
INNER JOIN lnkconfigurationitemtosinglecheck lkcg ON sgk.single_check_id = lk.single_check_id
INNER JOIN single_check_anag sgk ON sgk.single_check_id = lkcg.single_check_id
WHERE sgk.status = 'active' 
GROUP BY cc.NAME

Note that, you didn't specified any condition between the two tables complex_check_anag cc, comp_t_anag cmt, so you will get a cartesian product between the two tables and it might not give you correct data. So check the relation between these two tables and add a proper join type between them to get the correct data you are looking for.