MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table_a (id int, name varchar(100));
create table table_b (id int, name varchar(100), yourdate date);
insert into table_a values (1,'join');
insert into table_a values (2,'dave');
insert into table_a values (3,'henry');
insert into table_b values (1,'join','01.10.2022');
insert into table_b values (2,'dave','01.10.2022');
insert into table_b values (1,'join','06.10.2022');
insert into table_b values (2,'dave','06.10.2022');
insert into table_b values (1,'join','04.10.2022');
insert into table_b values (2,'dave','04.10.2022');
insert into table_b values (4,'roger','01.10.2022');
insert into table_b values (4,'roger','06.10.2022');
insert into table_b values (4,'roger','04.10.2022');
select * from table_b;


id
name
yourdate


1
join
2001-10-20

2
dave
2001-10-20

1
join
2006-10-20

2
dave
2006-10-20

1
join
2004-10-20

2
dave
2004-10-20

4
roger
2001-10-20

4
roger
2006-10-20

4
roger
2004-10-20


SELECT id, name, MAX(yourdate) AS date
FROM table_b GROUP BY id,name;


id
name
date


1
join
2006-10-20

2
dave
2006-10-20

4
roger
2006-10-20


SELECT b.id, b.name, MAX(b.yourdate) AS date
FROM table_b AS b JOIN table_a AS a ON a.id=b.id 
GROUP BY b.id,b.name;


id
name
date


1
join
2006-10-20

2
dave
2006-10-20

id	name	yourdate
1	join	2001-10-20
2	dave	2001-10-20
1	join	2006-10-20
2	dave	2006-10-20
1	join	2004-10-20
2	dave	2004-10-20
4	roger	2001-10-20
4	roger	2006-10-20
4	roger	2004-10-20