MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `codes` (
  `code_father` longtext CHARACTER SET utf8mb4,
  `code_son` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `prices` (
  `code_son` varchar(22) CHARACTER SET utf8mb4 NOT NULL,
  `price` float,
  `date` date,
  KEY `code_son` (`code_son`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `codes`
(`code_father`,
`code_son`)
VALUES
('ABC000001','ADV000055');
('ABC000001','ADV000045');
('ABC000001','ADV000035');
('ABC000001','ADV000015');
('ABC000002','ADV000079');
('ABC000002','ADV000077');
('ABC000007','ADV000040');
('ABC000008','ADV000030');
INSERT INTO `prices`
(`code_son`,
`price`,
`date`)
VALUES
('ADV000055','29.99','2021-11-06');
('ADV000055','9.99','2021-12-04');
('ADV000055','9.99','2021-12-01');
('ADV000055','29.99','2021-12-06');
('ADV000045','1999.99','2021-11-03');
('ADV000035','29.99','2021-11-09');
('ADV000079','29.99','2021-11-21');
('ADV000077','29.99','2021-11-16');
('ADV000077','29.99','2021-12-04');
('ADV000040','29.99','2021-11-04');
('ADV000030','29.99','2021-11-26');
('ADV000030','29.99','2021-10-21');
SELECT c.code_father, c.code_son, p.price, p.date
FROM prices p
INNER JOIN (SELECT code_son, price, MAX(date)as date FROM prices GROUP BY code_son)as t1 USING(code_son, date)
LEFT JOIN codes c ON c.code_son = p.code_son
WHERE c.code_father = 'ABC000001'


code_father
code_son
price
date


ABC000001
ADV000055
29.99
2021-11-06