Show last line with left outer join

Question

I have a problem with my syntax, I need to show the last record using inner joins, I used this example (SQL join: selecting the last records in a one-to-many relationship) as a base but I still can not, always appears the first, can someone help me and show me I'm wrong? My code:

select dc.nome,ep.documento,cc.dataVenc,cv.* from contratoscliente cc 
inner join em_processo ep on ep.id = cc.em_processo_id 
inner join dadoscliente dc on dc.em_processo_id = ep.id  
join contratoscliente_has_contratosvenc cccv on cccv.contratoscliente_id = cc.id 
join contratosvenc cv on (cccv.contratosvenc_id = cv.id) 
left outer join contratosvenc cv2 on (cccv.contratosvenc_id = cv2.id AND 
    (cv.vencimento < cv2.vencimento OR (cv.vencimento = cv2.vencimento AND cv.id < cv2.id))) WHERE (cv2.id IS NULL or cv.id=cv2.id) GROUP BY ep.id;

my tables:

contratosclientes: contratosclientes

contratosclientes_has_contratosvenc: contratosclientes_has_contratosvenc

contratosvenc: contratosvenc

query result: Result Query Wrong

fields 'vencimento' and 'valororiginal' appear from the first result, i wanted it to appear from the last one... My SQL Tables:

    DROP TABLE IF EXISTS `contratoscliente`;
CREATE TABLE `contratoscliente`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `perfilCobranca` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `statusPerfil` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `tipoConta` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `formaEnvio` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `dataVenc` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `enderecFatura` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `formaPagto` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `controleConta` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `contaDetalhadaInternet` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `icms` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `dataStatusCobranca` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `statusCobranca` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `dataFechamento` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `compartilhavel` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `statusPerfilFaturamento` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `programacaofaturamento` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `em_processo_id` int(11) NOT NULL,
  `dado_processado_em` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_dadoscliente_em_processo1_idx`(`em_processo_id`) USING BTREE,
  CONSTRAINT `contratoscliente_ibfk_1` FOREIGN KEY (`em_processo_id`) REFERENCES `em_processo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1946 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

INSERT INTO `contratoscliente` VALUES (1945, '', '05050505', 'Ativo', 'Resumida', 'Papel', '15', 'restrict', 'Fatura', 'Marcada', 'Marcada', 'Padrão', ' ', 'Aviso de rescisão contratual', '26', '', 'Ativo', ' ', 86, '2019-09-04 17:03:33');

DROP TABLE IF EXISTS `contratoscliente_has_contratosvenc`;
CREATE TABLE `contratoscliente_has_contratosvenc`  (
  `contratoscliente_id` int(11) NOT NULL,
  `contratosvenc_id` int(11) NOT NULL,
  PRIMARY KEY (`contratoscliente_id`, `contratosvenc_id`) USING BTREE,
  INDEX `contratoscliente_id`(`contratoscliente_id`) USING BTREE,
  INDEX `contratosvenc_id`(`contratosvenc_id`) USING BTREE,
  CONSTRAINT `contratoscliente` FOREIGN KEY (`contratoscliente_id`) REFERENCES `contratoscliente` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `contratovenc` FOREIGN KEY (`contratosvenc_id`) REFERENCES `contratosvenc` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;


INSERT INTO `contratoscliente_has_contratosvenc` VALUES (1945, 23358);
INSERT INTO `contratoscliente_has_contratosvenc` VALUES (1945, 23359);
INSERT INTO `contratoscliente_has_contratosvenc` VALUES (1945, 23360);
INSERT INTO `contratoscliente_has_contratosvenc` VALUES (1945, 23361);

DROP TABLE IF EXISTS `contratosvenc`;
CREATE TABLE `contratosvenc`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vencimento` date NULL DEFAULT NULL,
  `valorOriginal` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `valorAjuste` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `valorFinal` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `historicoFatura` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `dataPagamento` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `detalhesPagamento` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 23362 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
INSERT INTO `contratosvenc` VALUES (23358, '2019-02-15', 'R$ 71.28', 'R$ 0.00', 'R$ 71.28', 'Paga', '19/02/2019', 'Caixa econômica Federal - LBX Pagamento Loterica');
INSERT INTO `contratosvenc` VALUES (23359, '2019-03-15', 'R$ 86.49', 'R$ 0.00', 'R$ 86.49', 'Não Paga', ' ', ' ');
INSERT INTO `contratosvenc` VALUES (23360, '2019-04-15', 'R$ 85.00', 'R$ 0.00', 'R$ 85.00', 'Não Paga', ' ', ' ');
INSERT INTO `contratosvenc` VALUES (23361, '2019-05-15', 'R$ 85.00', 'R$ 0.00', 'R$ 85.00', 'Não Paga', ' ', ' ');


DROP TABLE IF EXISTS `dadoscliente`;
CREATE TABLE `dadoscliente`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `endereco` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `contato_principal` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `telefone_principal` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `telefone_residencial` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `telefone_comercial` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `email` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `em_processo_id` int(11) NOT NULL,
  `dado_processado_em` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_dadoscliente_em_processo1_idx`(`em_processo_id`) USING BTREE,
  CONSTRAINT `fk_dadoscliente_em_processo1` FOREIGN KEY (`em_processo_id`) REFERENCES `em_processo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1025 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;


INSERT INTO `dadoscliente` VALUES (1024, 'EFIGENIA CUSTODIA DE OLIVEIRA DE name teste', 'brazil', 'contatc', '(12) 12121212', '(12) 13131313', '', 'email@teste.com', 86, '2019-09-04 17:03:33');

DROP TABLE IF EXISTS `em_processo`;
CREATE TABLE `em_processo`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `documento` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `processado` int(11) NULL DEFAULT 0,
  `id_user_crm` int(11) NOT NULL,
  `servico` int(11) NOT NULL DEFAULT 1,
  `atuando` datetime(0) NULL DEFAULT NULL,
  `arquivo` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `id`(`id`, `documento`, `processado`, `id_user_crm`, `atuando`, `arquivo`) USING BTREE,
  INDEX `arquivo`(`arquivo`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 90 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

INSERT INTO `em_processo` VALUES (86, '0909090909', 1, 2, 6, '2019-09-04 17:03:08', 62);
INSERT INTO `em_processo` VALUES (87, '08080808', 0, 2, 6, '2019-09-04 17:03:08', 62);
INSERT INTO `em_processo` VALUES (89, '07070707', 0, 2, 6, '2019-09-04 17:03:08', 62);

`join contratosvenc cv on (cccv.contratosvenc_id = cv.id)` you are joining on primary key - so there can be only one row. So "first" or "last" is meaningless. — Paul Spiegel, Sep 05 '19 at 16:14
and how do I adapt it to try on all of contratoscliente_has_contratosvenc with the same contratosvenc_id ? — Leonardo, Sep 05 '19 at 16:20
Please post your table data as text in code blocks (instead of posting links to screenshots). — Paul Spiegel, Sep 05 '19 at 16:28

Paul Spiegel · Accepted Answer · 2019-09-05T18:28:23.933

Your case is not as "simple" as in the referenced question. Since you have a many-to-many relation, you need to "LEFT JOIN the INNER JOIN" of two tables (contratoscliente_has_contratosvenc and contratosvenc). It should be something like the following:

select dc.nome,ep.documento,cc.dataVenc,cv.*
from contratoscliente cc 
inner join em_processo ep on ep.id = cc.em_processo_id 
inner join dadoscliente dc on dc.em_processo_id = ep.id  
join contratoscliente_has_contratosvenc cccv on cccv.contratoscliente_id = cc.id 
join contratosvenc cv on (cccv.contratosvenc_id = cv.id) 

-- need to compare all rows from contratosvenc table
-- with the same contratoscliente_id in contratoscliente_has_contratosvenc table
left join (contratoscliente_has_contratosvenc cccv2
     join  contratosvenc cv2 on cccv2.contratosvenc_id = cv2.id 
) on cccv2.contratoscliente_id = cc.id AND
    (cv.vencimento < cv2.vencimento OR (cv.vencimento = cv2.vencimento AND cv.id < cv2.id))
WHERE cv2.id IS NULL

GROUP BY ep.id;

See db-fiddle demo

However - this is IMHO as good as unreadable. I would rather try another approach using LIMIT 1 subquery in the ON clause:

select dc.nome,ep.documento,cc.dataVenc,cv.*
from contratoscliente cc 
inner join em_processo ep on ep.id = cc.em_processo_id 
inner join dadoscliente dc on dc.em_processo_id = ep.id  
join contratoscliente_has_contratosvenc cccv
  on  cccv.contratoscliente_id = cc.id 
  and cccv.contratosvenc_id = (
    select cv2.id
    from contratoscliente_has_contratosvenc cccv2
    join contratosvenc cv2 on cccv2.contratosvenc_id = cv2.id 
    where cccv2.contratoscliente_id = cc.id
    -- get the "latest" row only
    order by cv2.vencimento desc, cv2.id desc
    limit 1
  )
join contratosvenc cv on  cccv.contratosvenc_id = cv.id

The third approach is to use a NOT EXISTS subquery. It is equivalent to the LEFT-JOIN-WHERE-NULL method, but IMHO more expressive.

select dc.nome,ep.documento,cc.dataVenc,cv.*
from contratoscliente cc 
inner join em_processo ep on ep.id = cc.em_processo_id 
inner join dadoscliente dc on dc.em_processo_id = ep.id  
join contratoscliente_has_contratosvenc cccv on cccv.contratoscliente_id = cc.id 
join contratosvenc cv on (cccv.contratosvenc_id = cv.id) 
-- get the row if there doesn't exist a "more current" row
where not exists (
  select *
  from contratoscliente_has_contratosvenc cccv2
  join contratosvenc cv2 on cccv2.contratosvenc_id = cv2.id 
  where cccv2.contratoscliente_id = cc.id
    and (cv.vencimento < cv2.vencimento OR (cv.vencimento = cv2.vencimento AND cv.id < cv2.id))
)

demo

bro i tested and did not solve, look at the img https://imgur.com/Co63R68 , is showing the month 02 I wanted you to show the last (in the case of the month 05) — Leonardo, Sep 05 '19 at 17:04
Well I have no data to test. Consider to create a fiddle [here](https://www.db-fiddle.com/) — Paul Spiegel, Sep 05 '19 at 17:06
i dont said that this are simples: you can help-me? it is "selecting the last records in a many-to-many relationship"? — Leonardo, Sep 05 '19 at 18:11
Fixed that. But the "LEFT JOIN the INNER JOIN of two tables" makes my brain overheat ;-) - Try the other approach. — Paul Spiegel, Sep 05 '19 at 18:16

Show last line with left outer join

1 Answers1