CODEWITHSUNDEEP
mysqlsqlgroup-concat
Undermine2k
Undermine2k

Reputation: 1491

Incorrect results when using GROUP_CONCAT with a where clause condition

I have a table like this:

ID | GenEx   | CodeName | Desc
----------------------------
1  | Cipro   | Dolvo    | 
2  | Ludavil | Ymir     | 
3  | Cipro   | Alpha    |

My query is like this:

SELECT GenEx, GROUP_CONCAT(CodeName) AS Code 
FROM Drugs D 
WHERE `CodeName` IN ('Alpha')
GROUP BY GenEx;

The results I want are:

| Genex |    Code     |
+-------+-------------+
| Cipro | Dolvo,Alpha |

The results I get are:

| Genex |    Code     |
+-------+-------------+
| Cipro | Alpha,Alpha |

The WHERE IN() clause causes the GROUP_CONCAT to replace anything returned to match that restricting set. How can I get it to match the codes that are outside of that set, as long as Alpha is included?

Upvotes: 1

Views: 245

Answers (2)

David Faber
David Faber

Reputation: 12485

You could try this if you don't want to use a subquery (for whatever reason):

SELECT GenEx, GROUP_CONCAT(CodeName) AS Code 
  FROM Drugs D 
 GROUP BY GenEx
HAVING 'Alpha' REGEXP GROUP_CONCAT(CodeName SEPARATOR '|');

or:

SELECT GenEx, GROUP_CONCAT(CodeName) AS Code 
  FROM Drugs D 
 GROUP BY GenEx
HAVING CONCAT(',',GROUP_CONCAT(CodeName),',') LIKE '%,Alpha,%';

But a subquery would almost certainly be more efficient than either of the two queries above.

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16691

I would first write a subquery that gets which genex have the alpha code:

SELECT DISTINCT genex
FROM drugs
WHERE codeName = 'Alpha';

Then, you can use that as your IN clause so it only includes the genex that will have an Alpha in the group concat list:

SELECT genex, GROUP_CONCAT(code_name)
FROM drugs
WHERE genex IN (
   SELECT DISTINCT genex
   FROM drugs
   WHERE codeName = 'Alpha')
GROUP BY genex;

EDIT

A minor note regarding your subquery, you can still replace the WHERE =with IN, if you wanted to check for multiple codes down the line:

SELECT DISTINCT genex
FROM drugs
WHERE codeName IN ('Alpha');

Here is an SQL Fiddle example.

Upvotes: 2

Related Questions