Patients Seen Across Several Specialties — SQL Joins Problem
Care coordinators want to know which patients are being seen by two or more different specialties, since those patients need a combined care plan.
- Difficulty: Medium
- Topics: Joins, Aggregation, Strings
- Dialect: MySQL
- Problem: #210
Problem statement
Care coordinators want to know which patients are being seen by two or more different specialties, since those patients need a combined care plan.
Return patient_id, name and specialties — the distinct specialties the patient has visited, sorted alphabetically and joined with ', ' (comma and space). Only patients with at least two distinct specialties appear. Order by patient_id.
Tables
Table: Patient
| Column | Type |
|---|---|
| patient_id | int |
| name | varchar |
Primary key: patient_id.
Table: Doctor
| Column | Type |
|---|---|
| doctor_id | int |
| name | varchar |
| specialty | varchar |
Primary key: doctor_id.
Table: Visit
| Column | Type |
|---|---|
| visit_id | int |
| patient_id | int |
| doctor_id | int |
| visit_date | date |
Primary key: visit_id.
One row per consultation. Both ids always exist in their tables.
Examples
Example 1
Patient
| patient_id | name |
|---|---|
| 1 | Aarav Sharma |
| 2 | Diya Reddy |
| 3 | Kabir Singh |
| 4 | Meera Das |
Doctor
| doctor_id | name | specialty |
|---|---|---|
| 11 | Kavya Menon | Cardiology |
| 12 | Rohan Gupta | Cardiology |
| 13 | Aisha Khan | Neurology |
| 14 | Ira Bose | ENT |
Visit
| visit_id | patient_id | doctor_id | visit_date |
|---|---|---|---|
| 1 | 1 | 11 | 2025-01-10 |
| 2 | 1 | 13 | 2025-01-24 |
| 3 | 1 | 14 | 2025-02-02 |
| 4 | 2 | 11 | 2025-01-11 |
| 5 | 2 | 12 | 2025-02-11 |
| 6 | 3 | 14 | 2025-01-15 |
| 7 | 3 | 12 | 2025-01-30 |
| 8 | 3 | 14 | 2025-03-01 |
Output
| patient_id | name | specialties |
|---|---|---|
| 1 | Aarav Sharma | Cardiology, ENT, Neurology |
| 3 | Kabir Singh | Cardiology, ENT |
How to solve Patients Seen Across Several Specialties
Join visits to doctors so each visit carries the doctor's specialty, then reduce those rows to distinct (patient, specialty) pairs and group them by patient. The filter is on the group: HAVING COUNT(*) >= 2 over the distinct pairs. The de-duplication is essential — a patient who saw two cardiologists, or the same ENT twice, has several visits but only one specialty and must not appear.
The list itself is GROUP_CONCAT over those distinct pairs, so each specialty is named once, with ORDER BY specialty so the list is alphabetical (without it the order would depend on how rows were read, and the answer would not be deterministic), and SEPARATOR ', ' for the comma-and-space format. MySQL's default separator is a bare comma.
In MySQL you can also write GROUP_CONCAT(DISTINCT specialty ORDER BY specialty SEPARATOR ', ') with COUNT(DISTINCT specialty) straight over the joined visits; the pair table makes the de-duplication explicit. The filter can equally be a correlated COUNT(DISTINCT …) subquery per patient, or the pairs can live in a CTE. Either way it is one join pass and one group per patient.
Reference solution (MySQL)
SELECT p.patient_id, p.name,
GROUP_CONCAT(s.specialty ORDER BY s.specialty SEPARATOR ', ') AS specialties
FROM Patient p
JOIN (
SELECT DISTINCT v.patient_id, d.specialty
FROM Visit v JOIN Doctor d ON d.doctor_id = v.doctor_id
) s ON s.patient_id = p.patient_id
GROUP BY p.patient_id, p.name
HAVING COUNT(*) >= 2
ORDER BY p.patient_idAnother way
SELECT p.patient_id, p.name, GROUP_CONCAT(s.specialty ORDER BY s.specialty SEPARATOR ', ') AS specialties
FROM Patient p JOIN (SELECT v.patient_id, d.specialty FROM Visit v JOIN Doctor d ON d.doctor_id = v.doctor_id GROUP BY v.patient_id, d.specialty) s ON s.patient_id = p.patient_id
WHERE (SELECT COUNT(DISTINCT d2.specialty) FROM Visit v2 JOIN Doctor d2 ON d2.doctor_id = v2.doctor_id WHERE v2.patient_id = p.patient_id) >= 2
GROUP BY p.patient_id, p.name
ORDER BY p.patient_idAnother way
WITH seen AS (
SELECT DISTINCT v.patient_id, d.specialty
FROM Visit v JOIN Doctor d ON d.doctor_id = v.doctor_id
)
SELECT p.patient_id, p.name, GROUP_CONCAT(s.specialty ORDER BY s.specialty SEPARATOR ', ') AS specialties
FROM seen s JOIN Patient p ON p.patient_id = s.patient_id
GROUP BY p.patient_id, p.name
HAVING COUNT(*) > 1
ORDER BY p.patient_id← Cashless Claims Settled per Insurer per Month · Lab Tests Slower Than Their Usual Turnaround →