0

I have a problem with my syntax, I need to show the last record using inner joins, I used this example (https://stackoverflow.com/questions/2111384/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);
  • Show us what the output should be. – Rick James Sep 5 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.