Triple Join in SQL with unique output

Question

I currently have 3 tables (diagnosis, encounters, and encounter diagnosis). 1 is the link to see the table outputs

I currently wrote a code that gives me everything I want, but I am looking for the first time a diagnosis occurred. code and output

Here is the code. Can anyone tell me how to get rid of duplicates?

SELECT a.code, a.title, c.start_dts    
FROM edw_emr_ods.diagnoses AS a    
JOIN edw_emr_ods.encounter_diagnoses as b on a.diagnosis_id=b.diagnosis_id    
JOIN edw_emr_ods.encounters as c on b.encounter_id=c.encounter_id    
ORDER BY a.title ASC;

I tried grouping, but I get an error that a.code is not listed in the GROUP.
Sorry I'm new to SQL and trying to pass a test and this is a practice problem

How would you do it if you didn't have a triple join? Point is, you should reduce your problem before posting here, so you have a minimal example that anyone can just use in order to reproduce the problem themselves. — Ulrich Eckhardt, Feb 27 '18 at 21:50
They don't look like duplicates to me. They have different dates. — BJones, Feb 27 '18 at 21:52
Learn more about joins here https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins — Valli, Feb 27 '18 at 21:56

score 1 · Answer 1 · answered Feb 27 '18 at 22:00

I am looking for the first time a diagnosis occurred.

In this case, I think if you do a group by and using MIN for the date would help, more or less like that

SELECT a.code, a.title, MIN(c.start_dts)
FROM edw_emr_ods.diagnoses AS a
JOIN edw_emr_ods.encounter_diagnoses AS b on a.diagnosis_id=b.diagnosis_id
JOIN edw_emr_ods.encounters AS c on b.encounter_id=c.encounter_id
GROUP BY a.code, a.title
ORDER BY a.title;

score 0 · Answer 2 · answered Feb 27 '18 at 21:59

Your group by should be this:

GROUP BY a.code, a.title, c.start_dts

That's if you want separate rows with separate c.start_dts. If you just want the first start_dts, your code would be:

SELECT a.code, a.title, MIN(c.start_dts)

FROM edw_emr_ods.diagnoses AS a

JOIN edw_emr_ods.encounter_diagnoses as b on a.diagnosis_id=b.diagnosis_id

JOIN edw_emr_ods.encounters as c on b.encounter_id=c.encounter_id

GROUP BY a.code, a.title

ORDER BY a.title ASC;

Triple Join in SQL with unique output

2 Answers2