Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

mysql group_concat distinct + group by and a where condition

I have a query like this which works fine until I need to add a condition on colA

SELECT GROUP_CONCAT(DISTINCT colA) as colA, CONCAT (colB,colC,colD) AS group1 FROM tablename GROUP BY group1

colA will return things like:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

"AH,LO,CE"
"AH,LO"
"AH,DE"
"AH"
"LO,CE,DF"
"LO,DF"

I need to add the condition to the query where a specific colA value must be present such as "AH" however when I do this the results do not the other concat(distinct on colA so I end up with

SELECT GROUP_CONCAT(DISTINCT colA) as colA, CONCAT (colB,colC,colD) AS group1 FROM tablename WHERE colA = 'AH' GROUP BY group1

"AH"
"AH"
"AH"
"AH"

I want the other records concatenated like it shows in the first result but adding that one of the matching records must include the value, but as long as one matches then it still brings in all the others that match the group on Group1

"AH,LO,CE"
"AH,LO"
"AH,DE"
"AH"

Any help greatly appreciated. Thank you.

>Solution :

You can use HAVING and FIND_IN_SET like this:

SELECT GROUP_CONCAT(DISTINCT colA) as colA, CONCAT(colB, colC, colD) AS group1
FROM tablename
GROUP BY group1
HAVING FIND_IN_SET('AH', colA)

DB Fiddle

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading