Patients Seen Across Several Specialties

Medium
210.

Patients Seen Across Several Specialties

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

ColumnType
patient_idint
namevarchar

Primary key: patient_id.

Table: Doctor

ColumnType
doctor_idint
namevarchar
specialtyvarchar

Primary key: doctor_id.

Table: Visit

ColumnType
visit_idint
patient_idint
doctor_idint
visit_datedate

Primary key: visit_id.

One row per consultation. Both ids always exist in their tables.

Example

The rows must come back in the order shown.

Patient

patient_idname
1Aarav Sharma
2Diya Reddy
3Kabir Singh
4Meera Das

Doctor

doctor_idnamespecialty
11Kavya MenonCardiology
12Rohan GuptaCardiology
13Aisha KhanNeurology
14Ira BoseENT

Visit

visit_idpatient_iddoctor_idvisit_date
11112025-01-10
21132025-01-24
31142025-02-02
42112025-01-11
52122025-02-11
63142025-01-15
73122025-01-30
83142025-03-01

Output

patient_idnamespecialties
1Aarav SharmaCardiology, ENT, Neurology
3Kabir SinghCardiology, ENT
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
patient_idint
namevarchar

Primary key: patient_id.

Table: Doctor

ColumnType
doctor_idint
namevarchar
specialtyvarchar

Primary key: doctor_id.

Table: Visit

ColumnType
visit_idint
patient_idint
doctor_idint
visit_datedate

Primary key: visit_id.

One row per consultation. Both ids always exist in their tables.

Examples

Example 1

Patient

patient_idname
1Aarav Sharma
2Diya Reddy
3Kabir Singh
4Meera Das

Doctor

doctor_idnamespecialty
11Kavya MenonCardiology
12Rohan GuptaCardiology
13Aisha KhanNeurology
14Ira BoseENT

Visit

visit_idpatient_iddoctor_idvisit_date
11112025-01-10
21132025-01-24
31142025-02-02
42112025-01-11
52122025-02-11
63142025-01-15
73122025-01-30
83142025-03-01

Output

patient_idnamespecialties
1Aarav SharmaCardiology, ENT, Neurology
3Kabir SinghCardiology, 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_id

Another 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_id

Another 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 →