[SOLVED] Find patients admitted multiple times for the same primary diagnosis in SQL

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

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.

Solution

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).