MySQL 5.6

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

CREATE TABLE scan (
  t1id int, 
  t2id int, 
  num1 int,
  num2 int, 
  created_date date
  );
INSERT INTO scan VALUES
(1,1,11,111,'2022-11-01'),
(2,1,22,222,'2022-11-02'),
(3,1,33,333,'2022-11-03'),
(4,2,44,444,'2022-11-04'),
(5,2,55,555,'2022-11-05'),
(6,2,66,666,'2022-11-06'),
(7,3,77,777,'2022-11-07'),
(8,3,88,888,'2022-11-08'),
(9,4,99,999,'2022-11-09'),
(10,5,100,1000,'2022-11-10'),
(11,5,111,1111,'2022-11-11');

CREATE TABLE dir (
  t2id int, 
  val1 char(1),
  val2 char(1)
  );
INSERT INTO dir VALUES
(1,'X','A'),
(2,'X','B'),
(3,'X','C'),
(4,'X','D'),
(5,'Y','A'),
(6,'Y','B'),
(7,'Y','C');

Records: 11  Duplicates: 0  Warnings: 0



Records: 7  Duplicates: 0  Warnings: 0


select * from scan;
select * from dir;


t1id
t2id
num1
num2
created_date


1
1
11
111
2022-11-01

2
1
22
222
2022-11-02

3
1
33
333
2022-11-03

4
2
44
444
2022-11-04

5
2
55
555
2022-11-05

6
2
66
666
2022-11-06

7
3
77
777
2022-11-07

8
3
88
888
2022-11-08

9
4
99
999
2022-11-09

10
5
100
1000
2022-11-10

11
5
111
1111
2022-11-11




t2id
val1
val2


1
X
A

2
X
B

3
X
C

4
X
D

5
Y
A

6
Y
B

7
Y
C


SELECT d.val1, 
       d.val2, 
       s.num1, 
       s.num2, 
       mx_dt.max_dt
FROM scan AS s 
INNER JOIN dir AS d on s.t2id=d.t2id
INNER JOIN ( SELECT t2id, 
                    max(created_date) as max_dt
             FROM scan 
             GROUP BY t2id
          )   as mx_dt on mx_dt.t2id = s.t2id and mx_dt.max_dt=s.created_date;



val1
val2
num1
num2
max_dt


X
A
33
333
2022-11-03

X
B
66
666
2022-11-06

X
C
88
888
2022-11-08

X
D
99
999
2022-11-09

Y
A
111
1111
2022-11-11

t1id	t2id	num1	num2	created_date
1	1	11	111	2022-11-01
2	1	22	222	2022-11-02
3	1	33	333	2022-11-03
4	2	44	444	2022-11-04
5	2	55	555	2022-11-05
6	2	66	666	2022-11-06
7	3	77	777	2022-11-07
8	3	88	888	2022-11-08
9	4	99	999	2022-11-09
10	5	100	1000	2022-11-10
11	5	111	1111	2022-11-11