MySQL group_concat с оператором 'in'

Question

У меня есть две таблицы, как определено:

Таблица 1: Пациент

| patient_id     | patient_name     |  medications |
------------------------------------------------------
| 1              | Jon Doe          |           1  |
| 2              | Jane Doe         |         2,3  |

Таблица 2: Лекарства

|  medication_id | medication_name  |
--------------------------------------
| 1              |           Med 1  |
| 2              |           Med 2  |
| 3              |           Med 3  |

Я создал схему и данные с помощью следующего sql:

create table patient (patient_id INT, patient_name TEXT, medications TEXT);
create table medication (medication_id INT, medication_name TEXT);
INSERT INTO patient VALUES (1, 'Jon Doe', '1');
INSERT INTO patient VALUES (2, 'Jane Doe', '2,3');
INSERT INTO medication VALUES (1, 'Med 1');
INSERT INTO medication VALUES (2, 'Med 2'); 
INSERT INTO medication VALUES (3, 'Med 3');

Я пытаюсь написать запрос, который перечислит пациентов с одним из столбцов, представляющих список лекарств, разделенных запятыми. Я пробовал следующий запрос:

SELECT
  patient_id,
  patient_name,
  medications,
  (SELECT group_concat(medication_name) FROM medication T2 WHERE T2.medication_id in (T1.medications)) as med_names
FROM
  patient T1

Проблема в том, что для второго пациента (Джейн Доу) я получаю только первое лекарство. Результат:

|  patient_id      | patient_name     | medications      | med_names   |
------------------------------------------------------------------------
|  1               | Jon Doe          | 1                | Med 1       |
|  2               | Jane Doe         | 2,3              | Med 2       |

Желаемый результат:

|  patient_id      | patient_name     | medications      | med_names     |
--------------------------------------------------------------------------
|  1               | Jon Doe          | 1                | Med 1         |
|  2               | Jane Doe         | 2,3              | Med 2, Med 3  |

Это возможно? Любая помощь будет оценена. Благодарю.

Answer 1

Вы поместили несколько значений в поля ваших лекарств в виде строки. Когда это строковое значение (2,3) сравнивается с целочисленными идентификаторами в фактической таблице med, строка будет преобразована в int. Это означает

JOIN ... ON '2,3' = x

будет проанализирован / выполнен как

JOIN ... ON 2 = x

Это плохой дизайн стола. Никогда не помещайте несколько значений в одно поле. Вы должны иметь нормализованный дизайн, что означает добавление patients_meds столы, где бы вы были patientID, medID записей.

Прочитайте немного: http://en.wikipedia.org/wiki/Database_normalization