2

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?

0

2 Answers 2

1

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.

0
0

Your query will select distinct combinations of the tables, but you want de-duplicated results from only one. To do this, limit your selection to the first table:

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

Or simply:

SELECT DISTINCT persona.* FROM `default_tb_persona` persona, `default_tb_envio` 
WHERE persona.`ci` = `default_tb_envio`.`id_persona_envia;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.