Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Select distinct * will filter rows only if all the values in each row returned by the query are identical. If you want to get only the fields from default_tb_persona, you need to replace the "*" with the list of column names. If you leave the distinct, that should remove the duplicates from the list of columns. –  Thomas Jones-Low Feb 27 '13 at 18:48
    
Hi @ThomasJones-Low, I modified the query and now have this: SELECT DISTINCT default_tb_persona.nombre_apellido, default_tb_persona.ci, default_tb_persona.telefono, default_tb_persona.direccion, correo, default_tb_envio.codigo FROM default_tb_persona LEFT JOIN default_tb_envio ON default_tb_persona.ci = default_tb_envio.id_persona_envia but still getting repeated results, how can avoid this? –  ReynierPM Feb 27 '13 at 18:56
    
possibly GROUP BY would help you. –  Max Vernon Feb 27 '13 at 19:17
    
Totally unrelated to the question but why do you have (the two joined columns) one as varchar(255) and the other as varchar(15)? –  ypercube Feb 27 '13 at 20:23
    
@ypercube well this is a ugly old design not from me, I'm not the one who design the DB in first I just try to import data for another system –  ReynierPM Feb 27 '13 at 20:24

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
your query works and I didn't get repeated results was my bad, see the difference under CI field, just the name are the same and I didn't see it before [12] => ([nombre_apellido] => Armando Serrano Tillan [ci] => 014568 [telefono] => 0416-5438093 [direccion] => [correo] => armando.serrano6@gmail.com ) [13] => ( [nombre_apellido] => Armando Serrano Tillan [ci] => 0145681 [telefono] => 0416-5438093 [direccion] => [correo] => armando.serrano6@gmail.com), thanks to every one –  ReynierPM Feb 28 '13 at 13:28

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;
share|improve this answer
    
Hi @bryan-agee, I've tried your second query and results aren't the same as the first one. First query returns 5432 rows, second 1422, why? Is this right? –  ReynierPM Feb 28 '13 at 13:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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