SQL Join to the latest record in mySQL

This does not solve my problem
https://stackoverflow.com/questions/50605160/sql-join-to-the-latest-record

I want to join tables in such a way that it fetches only the latest record from one of the tables using MySQL and also count the read_reciept columns with specific value

The following are my datas.

Table_One:

Table_two:

Results needed after Join:

Assuming you're up to an 8.0 release, you have two options:

1. LATERAL JOIN. Just a docs link, because it's less effficient and (for MySQL) more awkward to write.
2. Windowing function:

SELECT ID, Name,VisitID, Date
FROM (
    SELECT t1.ID, t1.Name, t2.VisitID, t2.Date,
        row_number() over (PARTITION BY t1.ID ORDER BY t2.Date DESC) rn
    FROM Table_One t1
    INNER JOIN Table_Two t2 ON t2.ID = t1.ID
) t 
WHERE rn = 1

If you're on an older release, well condolences first of all. 5.7 and older don't even really qualify as a modern database, and hasn't for a long time. But this is still possible.

If you are using an old version of mysql then try this :

Steps to do it :

First we need to get the latest date :

select ID, MAX(Date) as max_date, sum(read_reciept) as read_reciept
from Table_two
group by ID

Then we get the related Visit_ID and sum(read_reciept):

SELECT a.ID, a.Visit_ID, t.max_date, t.read_reciept
FROM Table_two a
INNER JOIN (
  select ID, MAX(Date) as max_date, sum(read_reciept) as read_reciept
  from Table_two
  group by ID
) as t on a.ID = t.ID and a.Date = t.max_date

And Finally we join our data using INNER JOIN to get the expected result

SELECT a.ID, b.Name, a.Visit_ID, a.Date, t.read_reciept
FROM Table_two a
INNER JOIN Table_One b on a.ID = b.ID
INNER JOIN (
   select ID, MAX(Date) as max_date, sum(read_reciept) as read_reciept
   from Table_two
 group by ID
) as t on a.ID = t.ID and a.Date = t.max_date

Demo here

Use sub-query with row_number to get only the last row from that table_two.

select Table_One.ID, name, Visit_ID, date_
from Table_One
join (select ID, Visit_ID, date_, row_number() over (partition by id order by date_ desc) rn
      from Table_two) t2
on (t2.ID = Table_One.ID and t2.rn=1)

Here row_number will number row with the same ID based on order by date column.

Later during join we take only rows with the value being produced equal 1, which means in this case - oldest.