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?

Solution

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.