Inner join 2 tabelas and get last result

I’m having trouble writing the query that returns the last id by date of a table and fetching the name of that id from another table

SELECT l1.* 
FROM ficha_eventos l1 
INNER JOIN (SELECT l2.`SQ_FICHA`, MAX(l2.`id`) as maxid 
            FROM ficha_eventos l2 
            GROUP BY l2.`SQ_FICHA`
           ) l3 ON l3.maxid = l1.id 

Result above

2| 1 |2014-04-04 | ok
6| 2 |2014-04-04 | ok
4| 3 |2014-04-04 | ok

ficha_cadastro

SQ_FICHA|NOME|

1|gabriel
2|carlos
3|manuel

ficha_evento

id|dia_atendimento|ligacao_atendimento|SQ_FICHA

1 | 2011-01-01 | no | 1
2 | 2014-04-04 | ok | 1
3 | 2012-01-01 | no | 2
4 | 2013-03-03 | ok | 3
5 | 2013-01-01 | no | 3
6 | 2014-04-04 | ok | 2

Result I want

2|gabriel|2014-04-04 | ok
6|carlos|2014-04-04 | ok
4|manuel|2014-04-04 | ok

Answer

With mysql 5.x you can use inner joins to acheive what you want.

but manuel has not the result you has shown us, it must be 2013-03-03 instead

the joins can be If you have more elements than events and still want to see them you must use RIGHT JOIN

CREATE TABLE ficha_cadastro (
  `SQ_FICHA` INTEGER,
  `NOME` VARCHAR(7)
);

INSERT INTO ficha_cadastro
  (`SQ_FICHA`, `NOME`)
VALUES
  ('1', 'gabriel'),
  ('2', 'carlos'),
  ('3', 'manuel');

CREATE TABLE ficha_evento (
  `id` INTEGER,
  `dia_atendimento` Date,
  `ligacao_atendimento` VARCHAR(2),
  `SQ_FICHA` INTEGER
);

INSERT INTO ficha_evento
  (`id`, `dia_atendimento`, `ligacao_atendimento`, `SQ_FICHA`)
VALUES
  ('1', '2011-01-01', 'no', '1'),
  ('2', '2014-04-04', 'ok', '1'),
  ('3', '2012-01-01', 'no', '2'),
  ('4', '2013-03-03', 'ok', '3'),
  ('5', '2013-01-01', 'no', '3'),
  ('6', '2014-04-04', 'ok', '2');

✓

✓

✓

✓

SELECT 
    f.id, f.dia_atendimento, fa.NOME, f.ligacao_atendimento
FROM
    ficha_evento f
        INNER JOIN
    (SELECT 
        MAX(dia_atendimento) daxdate, SQ_FICHA
    FROM
        ficha_evento
    GROUP BY SQ_FICHA) f1 ON f1.daxdate = f.`dia_atendimento`
        AND f1.SQ_FICHA = f.SQ_FICHA
        INNER JOIN
    ficha_cadastro fa ON fa.SQ_FICHA = f.SQ_FICHA
ORDER BY f.SQ_FICHA

id | dia_atendimento | NOME    | ligacao_atendimento
-: | :-------------- | :------ | :------------------
 2 | 2014-04-04      | gabriel | ok                 
 6 | 2014-04-04      | carlos  | ok                 
 4 | 2013-03-03      | manuel  | ok                 

db<>fiddle here