CODEWITHSUNDEEP
mysqlgroup-concatsubquery
Craig Austin
Craig Austin

Reputation: 1

MySQL group_concat with 'in' statement

I have two tables as defined:

Table 1: Patient

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

Table 2: Medication

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

I created the schema and data with the following 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');

I'm trying to write a query that will list the patients with one of the columns being a comma separated list of medications. I've tried the following query:

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

The problem is that for the 2nd patient (Jane Doe) I'm only getting the first medication. Result:

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

The desired result is:

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

Is this possible? Any help would be appreciated. Thanks.

Upvotes: 0

Views: 203

Answers (1)

Marc B
Marc B

Reputation: 360592

You've put multiple values into your meds fields, as a string. When that string value (2,3) is compared to the integer IDs in the actual med table, the string will be converted to an int. That means

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

will be parsed/executed as

JOIN ... ON 2 = x

This is a bad table design. Never put multiple values into a single field. You should have a normalized design, which means adding a patients_meds tables, where you'd have patientID, medID records.

Do some reading: http://en.wikipedia.org/wiki/Database_normalization

Upvotes: 2

Related Questions