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 .

Just fyi, I don't even consider looking at the queries as long as they are formatted like this. Actually they aren't formatted at all. And while you're at it, you could familiarize yourself with the join syntax that was made standard in 1992 and use it right away. It's just an unreadable mess. — fancyPants
– fancyPants, Commented May 4, 2015 at 15:00

Alex · Accepted Answer · 2015-05-04 15:34:17Z

Here are some suggestion to you:

CONCAT function accept as many parameters as you wish, so you don't need to concat(concat(concat...
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).
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

Collectives™ on Stack Overflow

group_concat duplicate values

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related