1527. Patients With a Condition

Day 3 SQL -> Solution + Explanation

Table: Patients

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
patient_id is the primary key for this table.
'conditions' contains 0 or more code separated by spaces. 
This table contains information of the patients in the hospital.

Write an SQL query to report the patient_id, patient_name and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Patients table:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
Output: 
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+
Explanation: Bob and George both have a condition that starts with DIAB1.

Solution:

Now, many of us will try to do this in our first attempt , I did the same in my first attempt after reading the question.

select patient_id, patient_name, conditions from patients where conditions like 'DIAB10%';

But, damn it shows up with WRONG ANSWER in red. haha!

snippet by the author

Because our solution, doesn’t satisfy the testcase , which is it not only wants the patients whose condistion starts with DIAB1 but also wants the records which contains DIAB1. For eg, in above snippet it expects ACNE DIAB100 also.

select patient_id, patient_name, conditions from patients where conditions like 'DIAB10%' or conditions like '% DIAB10%';

So, to cover the second condition we will use OR statement with LIKE operator. EASY!