Different database engines have varying ways to get the top row from table 2 per group (you can google for "SQL windowing functions" and your product). Since you don't state what engine you're using it's impossible to give the most appropriate or most performant solution.

The following method should work in most or all SQL engines but will not be especially performant over a large data set (it will benefit from a composite index Table2(ID, Date)). The details of how you specify the aliases for the tables may differ a bit among engines but you can use this as a guide. A windowing function solution will probably be more efficient.

SELECT ID, Name, VisitID, Date FROM Table1 T1 INNER JOIN Table2 T2 +
   ON T1.ID = T2.ID 
   WHERE NOT EXISTS (SELECT * FROM Table2 T2B WHERE T2B.ID = T1.ID AND T2B.Date > T2.Date)

I suspect you have SQL Server if so, then you can use APPLY :

select o.*, tt.*
from Table_One o 
cross apply ( select top 1 t.VisitDate, t.Date
              from table_two t
              where t.id = o.id
              order by t.date desc
            ) tt;

SELECT ID,Name,Visit_ID,Date
FROM
    (SELECT *, ROW_NUMBER() OVER(PARTITION BY ID Date DESC) as seq
    FROM Table2 LEFT OUTER JOIN
         Table1 ON Table2.ID = Table1.ID) as mainTable
WHERE seq = 1

I'm not a 100% sure if this is correct since the Visit ID might just throw every record right back at you. However you can find some great documentation here: https://www.w3resource.com/sql/aggregate-functions/max-date.php

select t1.ID,t1.Name,t2.visit_ID, Max(t2.Date) from Table_Two t2
inner join Table_One t1
on(t2.ID=t1.ID)
group by t1.ID,t1.Name,t2.visit_ID

something like this should work though, i think that this is also the same as @Erwin Smout proposes

select a.ID, t1.Name, a.date,t2.Visit_ID (
select ID, max(date)'date' from Table_Two 
group by ID) a
inner join Table_One t1
    on( a.ID=t1.ID)
inner join Table_Two t2
    on(a.ID=t2.ID and a.Date=t2.Date)

You can filter out "latest visit" using

SELECT ID,MAX(DATE) FROM TABLE_TWO GROUP BY ID;

You then join that to TABLE_ONE (... ON .ID = <alias_of_your_aggregation>.ID) to pick up the Name column and then join that again to TABLE_TWO (... ON ID=ID AND DATE=DATE) if you need to pick up the VISIT_ID as well.

Specific DBMS's might have proprietary/idiosyncratic extensions typically serving the purpose of allowing the optimizer do a better job (e.g. allowing the optimizer to understand that the "joining back to TABLE_TWO can be eliminated). Thinking here of SELECT TOP 1 ... and the like.

This is the answer to your question

SELECT t1.ID, t1.Name, t2.visit_id, t2.Date 
FROM table1 t1 
INNER JOIN table2 t2 ON t1.ID = t2.ID 
WHERE NOT EXISTS (
    SELECT * FROM table2 t2b WHERE t2b.ID = t1.ID AND t2b.Date > t2.Date
)

This is a working version of Hasan's answer :

SELECT ID, Name, [Visit ID], Date
FROM (
    SELECT
        t1.*, t2.[Visit ID], t2.Date,
        ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY Date DESC) AS rn
    FROM Table_One t1
    JOIN Table_Two t2 ON t1.ID = t2.ID
) AS t
WHERE rn = 1;

The output is:

Note: The row returned for Anna is different to the one in the question, as the desired output is incorrect!

And here's a db<>fiddle