Find patients admitted multiple times for the same primary diagnosis in SQL-CodePudding

need help with the below SQL query Problem statement - Show patient_id, primary_diagnosis from admissions. Find patients admitted multiple times for the same primary_diagnosis

Table - admissions Table headers - patient_id, admission_date, discharge_date, primary_diagnosis, secondary_diagnosis

My code -

SELECT Distinct ad1.patient_id, ad1.primary_diagnosis
FROM admissions ad1 join admissions ad2
ON ad1.patient_id = ad2.patient_id AND
ad1.primary_diagnosis = ad2.primary_diagnosis
Group by ad1.patient_id
having count(ad1.patient_id)>1;

I was solving the problem online and the above code is not giving me the desired output. The portal doesn't tell what is wrong with the code. Please correct me if I am making a mistake in the logic. Thanks

CodePudding user response：

SELECT
  ad.patient_id,
  ad.primary_diagnosis
FROM
  admissions ad
GROUP BY
  ad.patient_id,
  ad.primary_diagnosis
HAVING
  COUNT(DISTINCT ad.admission_date) > 1

CodePudding user response：

Seems like writing a self join statement is unnecessary when you already have Group by and Having statement. Like the previous answer says, make sure to group by both patient_id and primary_diagnosis since you need to know what the same patient was diagnosed with (primary_diagnosis).