Mysql – How to get non repeat results

join;MySQL

I've two tables default_tb_persona and default_tb_envio, this are the DDL for those tables:

CREATE TABLE `default_tb_envio` (
  `codigo` varchar(7) NOT NULL,
  `fecha` date NOT NULL,
  `id_modalidad` int(10) NOT NULL,
  `costo` decimal(28,6) NOT NULL,
  `id_agencia` int(10) DEFAULT NULL,
  `no_envio` int(10) NOT NULL,
  `id_forma_pago` int(10) DEFAULT NULL,
  `bl` text,
  `id_destino` int(10) DEFAULT NULL,
  `id_persona_envia` varchar(255) DEFAULT NULL,
  `id_persona_recibe` varchar(255) DEFAULT NULL,
  `id_organismo` int(10) DEFAULT '1',
   PRIMARY KEY (`codigo`),
   UNIQUE KEY `envio_pkey` (`codigo`),
   KEY `fki_` (`id_persona_envia`),
   KEY `fki_1` (`id_persona_recibe`),
   KEY `envio_modalidad_fkey` (`id_modalidad`),
   KEY `id_foma_pago_fkey` (`id_forma_pago`),
   KEY `id_organismo_fk` (`id_organismo`),
   KEY `ndestino_fkey` (`id_destino`),
   KEY `tb_envio_id_agencia_fkey` (`id_agencia`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `default_tb_persona` (
  `nombre_apellido` varchar(255) NOT NULL,
  `ci` varchar(15) NOT NULL,
  `telefono` varchar(255) DEFAULT NULL,
  `direccion` text,
  `correo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ci`),
  UNIQUE KEY `tb_persona_pkey` (`ci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm trying to get non equal or repeat results from default_tb_persona using a JOIN sentence, this is what I've made for that:

SELECT DISTINCT * FROM 
  `default_tb_persona` 
LEFT JOIN 
  `default_tb_envio` 
ON 
  `default_tb_persona`.`ci` = `default_tb_envio`.`id_persona_envia`

But I'm getting repeated results. Is that query right? If I have two records pointing to the same record on tb_persona should the query return those two records? I've a test data if any can help me, just say me where to send and I will to solve this problem. Also it's possible to get only fields from default_tb_persona and not from both tables?

Best Answer

Try

SELECT DISTINCT dtp.ci, 
            dtp.nombre_apellido, 
            dtp.telefono, 
            dtp.direccion, 
            dtp.correo, 
            dte.codigo 
FROM   default_tb_persona dtp
   LEFT JOIN default_tb_envio dte
          ON dtp.ci = dte.id_persona_envia;

Max.

Related Question