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