SQL Join from Two Tables showing only maximum date in one table

Question

I have two tables, visits and encounters. Each Visit by a student may have several encounters, at different times. I would like a query with visitid, encounterid, and encounterdate showing ONLY the latest encounter for each visit, My results MUST include visits with no encounters.

My tables ;

Visits
visit_id
studenti_id

Encounters
encounter_id
visit_id
encounter_datetime

What I have tried

select 
       Visits.visit_id, 
       Encounters.encounter_id,
       Encounters.encounter_datetime
FRom Visits
     LEFT OUTER JOIN Encounters
     ON Visits.visit_id = Encounters.visit_id
    INNER JOIN ( 
          select  Encounters.visit_id, MAX(Encounters.encounter_datetime)as Latest 
          from Encounters 
          group by Encounters.visit_id
     ) as NewEncounters 
    ON Encounters.visit_id = NewEncounters.visit_id 
       AND Encounters.encounter_datetime = NewEncounters.Latest

This returns the results I want, HOWEVER, Visits without encounters are not in the results.

LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But you have to write many clear, concise & precise phrasings of your question/problem/goal to be able to search. PS Please in code questions give a [mre]. [ask] [Help] — philipxy, Apr 09 '22 at 08:16
"ONLY the latest encounter for each visit" Another faq. [Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) [Select first row in each GROUP BY group?](https://stackoverflow.com/q/3800551/3404097) Etc. — philipxy, Apr 09 '22 at 08:19
@TimBiegeleisen My comments are neutral, relevant & helpful. Moreover I didn't just give a link to the bug, I summarized the problem & how to approach in the future to avoid it. Moreover while you were writing a comment I was cutting & pasting a duplicate for the overall problem. Please don't jump to conclusions, or assume negative intent & regardless of what you expericence don't be rude. (user2750663--[answer] [meta] [meta.se] https://stackoverflow.com/conduct) — philipxy, Apr 09 '22 at 08:23
@TimBiegeleisen The question is poor, in a common pattern, where it gives a goal & broken unexplored/uninvestigated code & it is not clear whether the question is the bug or the overall goal. It should be closed until which they are asking is clear. I gave a link to both, I can only generate 1 link by flag, I also gave a dupe for the overall goal & mentioned it a comment to you before you posted your last comment. I'm done. — philipxy, Apr 09 '22 at 08:32
Provide an _exact_ duplicate and I will close and also delete my answer. — Tim Biegeleisen, Apr 09 '22 at 08:33
Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Apr 09 '22 at 08:56
Possible duplicate of [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/q/55094277/3404097) — philipxy, Apr 09 '22 at 09:56

score 0 · Answer 1 · answered Apr 09 '22 at 08:15

I actually don't see a clean way to salvage your direct join approach, but if your database support ROW_NUMBER, it is an easy option:

WITH cte AS (
    SELECT v.visit_id, e.encounter_id, e.encounter_datetime,
           ROW_NUMBER() OVER (PARTITION BY v.visit_id ORDER BY e.encounter_datetime DESC) rn
    FROM Visits v
    LEFT JOIN Encounters e ON v.visit_id = e.visit_id
)

SELECT visit_id, encounter_id, encounter_datetime,
FROM cte
WHERE rn = 1;

score 0 · Answer 2 · answered Apr 09 '22 at 08:20

For the problem of getting the max of several dates I give an (untested! Sorry) code example, which, however, points out the line of approach.

Select 
       Visits.visit_id, 
       a.encounter_id,
       max(a.encounter_datetime) as Max_Datetime
FRom Visits
     LEFT OUTER JOIN Encounters a
     ON Visits.visit_id = a.Encounters.visit_id
     inner join 
    Encounters b
    on a.visit_id=b.visit_id
    and
    a.encounter_datetime<=b.encounter_datetime
group by
       Visits.visit_id, 
       a.encounter_id,
       a.encounter_datetime;

For visits without encounters you can union a query with a where clause using Is Null. Maybe your database needs some syntactic fumbling with ; etc.

SQL Join from Two Tables showing only maximum date in one table

What I have tried

2 Answers2