SQL query multiple admission_date same patient id

Question

I am trying to query patients admitted multiple times for same diagnosis.

Show patient_id, diagnosis from admissions. Find patients admitted multiple times for the same diagnosis.

SELECT
    patient_id,
    diagnosis,admission_date
FROM 
    admissions
GROUP BY
    diagnosis,
    admission_date
HAVING
    COUNT(admission_date) > 1;

I thought maybe subquery would be better solution. But have no idea, how to query same diagnosed patient_id s

Answer 1

Without subquery:

SELECT a1.patient_id, a1.diagnosis
FROM admissions a1
JOIN admissions a2
ON a1.patient_id = a2.patient_id AND a1.diagnosis = a2.diagnosis
GROUP BY a1.patient_id, a1.diagnosis
HAVING COUNT(*) > 1

With subquery:

SELECT patient_id, diagnosis
FROM admissions
WHERE patient_id IN (
  SELECT patient_id
  FROM admissions
  GROUP BY patient_id, diagnosis
  HAVING COUNT(*) > 1
)

Hope it helps.