group_concat duplicate values

Question

I wrote two queries to concat values with different conditions, the results of two queries should be same,but I couldn't get correct result with query2.The two queries are as follows:

query1:

SELECT group_concat(concat(concat(concat(concat(concat(f.NAME, ';') , sgk.NAME),' ') ,cc.operator),' ')) as res_string
FROM complex_check_anag cc,lnksinglechecktocomplexcheck lk,single_check_anag sgk,functionalci f ,lnkconfigurationitemtosinglecheck lkcg 
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

with query1 I can get the following result:

res_string
---------------------------------------------------------------------------------------------------------- 
MIL04DNS01;memory check and ,MIL04DNS01;cpu_check and 
MIL04APPBOXIP01;cpu_check and ,MIL04APPBOXIP01;memory check and
Sito_Saturn_Secure;log in check and

Query2:

SELECT group_concat(concat(concat(concat(concat(concat(f.NAME, ';') , sgk.NAME),' ') ,cc.operator),' ')) as 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' and cc.complex_check_id <> cmt.comp_o_id GROUP BY sgk.NAME

with query2 I can get the following result:

res_string
-------------------------------------------------------------------------------------------------------------------------------
MIL04DNS01;memory check and ,MIL04DNS01;cpu_check and ,MIL04DNS01;memory check and ,MIL04DNS01;cpu_check and ,MIL04DNS01;memory check and ,MIL04DNS01;cpu_check and
MIL04APPBOXIP01;cpu_check and ,MIL04APPBOXIP01;memory check and ,MIL04APPBOXIP01;cpu_check and ,MIL04APPBOXIP01;memory check and ,MIL04APPBOXIP01;cpu_check and ,MIL04APPBOXIP01;memory check and 
Sito_Saturn_Secure;log in check and ,Sito_Saturn_Secure;log in check and ,Sito_Saturn_Secure;log in check and and ,MIL04DNS01;memory check and ,MIL04APPBOXIP01;memory check and  

could you give me some suffestions to modify the query2 to get the same result with query1 ?...Thanks a lot .

Answer 1

Here are some suggestion to you:

1. CONCAT function accept as many parameters as you wish, so you don't need to concat(concat(concat...

2. You should never use FROM tbl1,tbl2,tbl3... when JOIN tables. It should be clearly set by LEFT JOIN, RIGHT JOIN or INNER JOIN with ON rule(s).

3. Here my guess how your query should be like. But I have some problems to understand how last table comp_t_anag cmt must be joined. I don't see real key to join that table. And I did LEFT JOIN for all tables, maybe you need INNER somewhere, so you can play with that.

 SELECT GROUP_CONCAT(CONCAT(f.NAME, ';', sgk.NAME,' ',cc.operator,' ')) as res_string 
 FROM complex_check_anag cc
 LEFT JOIN lnksinglechecktocomplexcheck lk
 ON cc.complex_check_id = lk.complex_check_id 
 LEFT JOIN  single_check_anag sgk
 ON sgk.single_check_id = lk.single_check_id
   AND sgk.status = 'active'
 LEFT JOIN lnkconfigurationitemtosinglecheck lkcg
 ON sgk.single_check_id = lkcg.single_check_id 
 LEFT JOIN functionalci f 
 ON  f.id = lkcg.config_item_id 
 LEFT JOIN comp_t_anag cmt
 ON cc.complex_check_id <> cmt.comp_o_id
 GROUP BY sgk.NAME