CODEWITHSUNDEEP
mysql
Danny
Danny

Reputation: 1185

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:

"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.

Upvotes: 0

Views: 49

Answers (1)

Oleksii Tambovtsev
Oleksii Tambovtsev

Reputation: 2834

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

Upvotes: 1

Related Questions