group_concat in mysql with "case when " conditions

Question

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 !

I am sorry , I mean that there is error message when I execute the query — Anwar Ahmat, Apr 29 '15 at 09:15
That's closer, but still two more things: what was the error message, exactly, and what would be the correct result (what are you trying to achieve). Please put those in the question. — Cthulhu, Apr 29 '15 at 09:17
error message is : " You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cstatus,sgk.status sgstatus,cc.NAME complex_check_name,cc.min min_flag,cmt.comp_' at line 1 " — Anwar Ahmat, Apr 29 '15 at 09:17
correct result should be , when "case" condition is satisfied : "active active AN1WVS01 0 not MIL04APPBOXIP01;cpu_check and ,MIL04APPBOXIP01;memory check and " ; when "case" condition is not satisfied : "active active AQ1PVS01 0 AL1WVS01 MIL04DCW003;cpu test check and " — Anwar Ahmat, Apr 29 '15 at 09:21

Mahmoud Gamal · Accepted Answer · 2015-04-29T09:40:27.187

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.

Thanks a lot for your suggestions . I have tried bothe queries , but it doesn't work, there is error message near the "case when" condition.I am confusing to use "case when " with "Group_cancat" . Thanks a lot ! — Anwar Ahmat, Apr 29 '15 at 09:35
@AnwarAhmad - Sorry it is my fault, I forget a `,` before the case when, Try my updated query. — Mahmoud Gamal, Apr 29 '15 at 09:40

score 1 · Answer 2 · answered Jul 04 '16 at 09:35

1

select group_concat(concat( case when ProStatus='A' then round(proQnty) else 0 end,':',prodtmappid) separator',') as result from tblproductmapforlisting where
 prodtmappid in (329607,329606,329605,329604) order by FIELD(prodtmappid,329607,329606,329605,329604)

answered Jul 04 '16 at 09:35

Rahul Yadav

867
11
12

1

The answer should explain how it solves the problem and why. – Alex Jul 04 '16 at 12:45
Although this code may be help to solve the problem, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Jul 04 '16 at 16:44

group_concat in mysql with "case when " conditions

2 Answers2