How to remove or hide duplicate records from two detail tables with a master table in Mysql?

0

I have the following tables:

    CREATE TABLE 'COMPRAS' (
      'codigo' CHAR(10) CHARACTER SET utf8 NOT NULL,
      'periodo' SMALLINT(4) NOT NULL DEFAULT '0',
      'mes' SMALLINT(2) NOT NULL,
      'numero_indicador' INT(12) NOT NULL DEFAULT '0',
      'rut' CHAR(10) CHARACTER SET utf8 NOT NULL,
      'tipodocumento' SMALLINT(3) NOT NULL,
      'nombre' VARCHAR(90) NOT NULL,
      'nrodoc' INT(12) NOT NULL DEFAULT '0',
      'fecha' DATE NOT NULL DEFAULT '0000-00-00',
      'afecto' DOUBLE(15,0) NOT NULL DEFAULT '0',
      'exento' DOUBLE(15,0) NOT NULL DEFAULT '0',
      'iva' DOUBLE(15,0) NOT NULL DEFAULT '0',
      'total' INT(12) NOT NULL DEFAULT '0',
      'cuenta' SMALLINT(4) NOT NULL DEFAULT '0',
      'glosa' VARCHAR(200) NOT NULL,
      'credito_valor' DOUBLE(15,0) DEFAULT '0',
      'ivaretenido' DOUBLE(15,0) DEFAULT '0',
      'ivanorecibido' DOUBLE(15,0) DEFAULT '0',
      'direccion' VARCHAR(90) DEFAULT NULL,  
       PRIMARY KEY ('codigo','periodo','mes','numero_indicador')
    ) ENGINE=INNODB DEFAULT CHARSET=latin1;


    CREATE TABLE 'DETALLE_IMPUESTOS' (
      'id_detalleimpuestos' INT(20) NOT NULL AUTO_INCREMENT,
      'codigo' CHAR(10) CHARACTER SET utf8 NOT NULL,
      'periodo' SMALLINT(4) NOT NULL DEFAULT '0',
      'mes' SMALLINT(2) NOT NULL,
      'numero_indicador' INT(6) NOT NULL DEFAULT '0',
      'imptoadic' SMALLINT(3) NOT NULL DEFAULT '0',
      'pctadic' DOUBLE(5,2) NOT NULL DEFAULT '0.00',
      'valadic' INT(12) NOT NULL DEFAULT '0',
      PRIMARY KEY ('id_detalleimpuestos')
    ) ENGINE=INNODB DEFAULT CHARSET=latin1;


    CREATE TABLE 'DETALLE_COMPRAS' (
      'id_detallecompras' INT(20) NOT NULL AUTO_INCREMENT,
      'codigo' CHAR(10) CHARACTER SET utf8 NOT NULL,
      'periodo' SMALLINT(4) NOT NULL DEFAULT '0',
      'mes' SMALLINT(2) NOT NULL,
      'numero_indicador' INT(12) NOT NULL DEFAULT '0',
      'cuenta' SMALLINT(4) NOT NULL DEFAULT '0',
      'centro' SMALLINT(4) NOT NULL DEFAULT '0',
      'num_docto' INT(12) NOT NULL DEFAULT '0',
      'valor' INT(12) NOT NULL DEFAULT '0',
      'glosa' VARCHAR(200) NOT NULL,
      PRIMARY KEY ('id_detallecompras')
      ) ENGINE=INNODB DEFAULT CHARSET=latin1;

Where the table purchases is the teacher and the other two are detailed. When the crossing of the parent table with one of them, it is acceptable to be repeated in the parent table, but when the crossing of the three, I repeat a lot of records, staying like this:

The query I do is the following:

    SELECT l.codigo,l.periodo,l.mes,l.numero_indicador,l.rut,l.tipodocumento,l.nombre,l.nrodoc,DATE_FORMAT(l.fecha,'%d-%m-%Y') 
    AS fecha,l.afecto,l.exento,l.iva,l.total,l.cuenta,l.glosa,l.credito_valor,l.ivaretenido,l.ivanorecibido,l.direccion, 
    dlp.id_detalleimpuestos, dlp.pctadic,dlp.imptoadic,dlp.valadic,dl.id_detallecompras, dl.cuenta,dl.centro,dl.num_docto,dl.valor
    FROM COMPRAS l LEFT JOIN DETALLE_COMPRAS dl ON l.numero_indicador=dl.numero_indicador AND l.mes=dl.mes AND l.periodo=dl.periodo AND l.codigo=dl.codigo
    LEFT JOIN DETALLE_IMPUESTOS dlp ON l.numero_indicador=dlp.numero_indicador AND l.mes=dlp.mes AND l.periodo=dlp.periodo AND l.codigo=dlp.codigo 
    WHERE l.codigo='22-ene' AND l.periodo=2016 AND l.numero_indicador =110001 GROUP BY dlp.id_detalleimpuestos, dl.id_detallecompras ;

As I do so that I do not repeat the records that are marked in red according to the image.

NOTE: the detail tables have the foreign key codes , period , month , number_indicator

The PURCHASES table has a record with n table records DETAIL_IMPOSITIONS and with table DETAIL_COMPRESS p records. The purchase table is the heading of the detail tables

    
asked by Danilo 18.11.2016 в 02:10
source

1 answer

1

The problem you are having has to do with that the detail tables are 1 to M with the table COMPRAS , so it will not be possible to "remove" the "duplicate" records from the query. What's more, if you added another table to the query, the duplicates would grow.

To get the results you want, you should separate the questions in three .

A query for each table filtering through the field codigo .

This guarantees you that you do not have duplicate records.

Once the three queries have been obtained, you can load the objects in the application and display them as you wish.

    
answered by 18.11.2016 в 04:55