Hi.
i have a case that
i have 4 tables
1 tables
CREATE TABLE impor_600mm (
impor_600mm_id int(11) NOT NULL AUTO_INCREMENT,
inv_impor_kus_600mm varchar(30) DEFAULT NULL,
inv_impor_cn_600mm_date date DEFAULT NULL,
inv_impor_cn_600mm varchar(30) DEFAULT NULL,
tgl_pib_600mm date DEFAULT NULL,
no_pib_600mm varchar(15) DEFAULT NULL,
tgl_bl_impor_600mm date DEFAULT NULL,
no_bl_impor_600mm varchar(30) DEFAULT NULL,
tgl_manifest_600mm date DEFAULT NULL,
tgl_bongkar_600mm_mulai date DEFAULT NULL,
tgl_bongkar_600mm_selesai date DEFAULT NULL,
uraian_tipe_tiles_impor_600mm varchar(60) DEFAULT NULL,
pieces_impor_600mm float DEFAULT '0',
cartons_impor_600mm float DEFAULT '0',
sqm_impor_600mm float DEFAULT '0',
pecah_in_pieces_impor_600mm float DEFAULT '0',
pecah_in_sqm_impor_600mm float DEFAULT '0',
note_impor_600mm varchar(255) DEFAULT NULL,
auto_calculation_id int(11) DEFAULT '2',
tablename varchar(255) DEFAULT NULL,
timestamp timestamp NULL DEFAULT NULL,
PRIMARY KEY (impor_600mm_id),
KEY no_pib_600mm (no_pib_600mm),
KEY inv_impor_cn_600mm (inv_impor_cn_600mm)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;
=========================================================
second table
CREATE TABLE ekspor_600mm (
ekspor_600mm_id int(11) NOT NULL AUTO_INCREMENT,
impor_600mm_id int(11) DEFAULT NULL,
impor_600mm_out_id int(11) DEFAULT NULL,
tgl_inv_ekspor_kus_600mm date DEFAULT NULL,
inv_ekspor_kus_600mm varchar(30) DEFAULT NULL,
inv_impor_cn_600mm_date date DEFAULT NULL,
inv_impor_cn_600mm varchar(30) DEFAULT NULL,
tgl_peb_600mm date DEFAULT NULL,
no_peb_600mm varchar(15) DEFAULT NULL,
tgl_bl_ekspor_600mm date DEFAULT NULL,
no_bl_ekspor_600mm varchar(30) DEFAULT NULL,
tgl_muat_600mm_mulai date DEFAULT NULL,
tgl_muat_600mm_selesai date DEFAULT NULL,
uraian_tipe_tiles_ekspor_600mm varchar(60) DEFAULT NULL,
pieces_ekspor_600mm float DEFAULT '0',
cartons_ekspor_600mm float DEFAULT '0',
sqm_ekspor_600mm float DEFAULT '0',
sisa_pieces_600mm float DEFAULT '0',
sisa_sqm_600mm float DEFAULT '0',
apakah_ada_sisa_id int(11) DEFAULT NULL,
note_ekspor_600mm varchar(255) DEFAULT NULL,
tablename varchar(255) DEFAULT NULL,
timestamp timestamp NULL DEFAULT NULL,
PRIMARY KEY (ekspor_600mm_id),
KEY no_peb_600mm (no_peb_600mm),
KEY inv_impor_cn_600mm (inv_impor_cn_600mm)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
======================================================================
Third table
CREATE TABLE impor_800mm (
impor_800mm_id int(11) NOT NULL AUTO_INCREMENT,
inv_impor_kus_800mm varchar(30) DEFAULT NULL,
inv_impor_cn_800mm_date date DEFAULT NULL,
inv_impor_cn_800mm varchar(30) DEFAULT NULL,
tgl_pib_800mm date DEFAULT NULL,
no_pib_800mm varchar(15) DEFAULT NULL,
tgl_bl_impor_800mm date DEFAULT NULL,
no_bl_impor_800mm varchar(30) DEFAULT NULL,
tgl_manifest_800mm date DEFAULT NULL,
tgl_bongkar_800mm_mulai date DEFAULT NULL,
tgl_bongkar_800mm_selesai date DEFAULT NULL,
uraian_tipe_tiles_impor_800mm varchar(60) DEFAULT NULL,
pieces_impor_800mm float DEFAULT '0',
cartons_impor_800mm float DEFAULT '0',
sqm_impor_800mm float DEFAULT '0',
pecah_in_pieces_impor_800mm float DEFAULT '0',
pecah_in_sqm_impor_800mm float DEFAULT '0',
note_impor_800mm varchar(255) DEFAULT NULL,
auto_calculation_id int(11) DEFAULT '2',
tablename varchar(255) DEFAULT NULL,
timestamp timestamp NULL DEFAULT NULL,
PRIMARY KEY (impor_800mm_id)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8;
=======================================================================
Fourth table
CREATE TABLE ekspor_800mm (
ekspor_800mm_id int(11) NOT NULL AUTO_INCREMENT,
impor_800mm_id int(11) DEFAULT NULL,
impor_800mm_out_id int(11) DEFAULT NULL,
tgl_inv_ekspor_kus_800mm date DEFAULT NULL,
inv_ekspor_kus_800mm varchar(30) DEFAULT NULL,
inv_impor_cn_800mm_date date DEFAULT NULL,
inv_impor_cn_800mm varchar(30) DEFAULT NULL,
tgl_peb_800mm date DEFAULT NULL,
no_peb_800mm varchar(15) DEFAULT NULL,
tgl_bl_ekspor_800mm date DEFAULT NULL,
no_bl_ekspor_800mm varchar(30) DEFAULT NULL,
tgl_muat_800mm_mulai date DEFAULT NULL,
tgl_muat_800mm_selesai date DEFAULT NULL,
uraian_tipe_tiles_ekspor_800mm varchar(60) DEFAULT NULL,
pieces_ekspor_800mm float DEFAULT '0',
cartons_ekspor_800mm float DEFAULT '0',
sqm_ekspor_800mm float DEFAULT '0',
sisa_pieces_800mm float DEFAULT '0',
sisa_sqm_800mm float DEFAULT '0',
apakah_ada_sisa_id int(11) DEFAULT NULL,
note_ekspor_800mm varchar(255) DEFAULT NULL,
tablename varchar(255) DEFAULT NULL,
timestamp timestamp NULL DEFAULT NULL,
PRIMARY KEY (ekspor_800mm_id),
KEY no_peb_800mm (no_peb_800mm),
KEY inv_impor_cn_800mm (inv_impor_cn_800mm)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8;
==================================================================
mysql query to display the data
SELECT Coalesce(impor_600mm.tgl_pib_600mm, '-') AS tgl_pib_600mm,
Coalesce(impor_600mm.no_pib_600mm, '-') AS no_pib_600mm,
Coalesce(impor_600mm.tgl_bongkar_600mm_mulai, '-') AS tgl_bongkar_600mm_mulai,
Coalesce(impor_600mm.uraian_tipe_tiles_impor_600mm,
'-') AS uraian_tipe_tiles_impor_600mm,
Coalesce(impor_600mm.pieces_impor_600mm, '-') AS pieces_impor_600mm,
Coalesce(impor_600mm.pecah_in_pieces_impor_600mm,
'-') AS pecah_in_pieces_impor_600mm,
Coalesce(ekspor_600mm.tgl_peb_600mm, '-') AS tgl_peb_600mm,
Coalesce(ekspor_600mm.no_peb_600mm, '-') AS no_peb_600mm,
Coalesce(ekspor_600mm.tgl_muat_600mm_mulai, '-') AS tgl_muat_600mm_mulai,
Coalesce(ekspor_600mm.uraian_tipe_tiles_ekspor_600mm,
'-') AS uraian_tipe_tiles_ekspor_600mm,
Coalesce(ekspor_600mm.pieces_ekspor_600mm, '-') AS pieces_ekspor_600mm,
Coalesce(ekspor_600mm.sisa_pieces_600mm, '-') AS sisa_pieces_600mm,
Coalesce(impor_800mm.tgl_pib_800mm, '-') AS tgl_pib_800mm,
Coalesce(impor_800mm.no_pib_800mm, '-') AS no_pib_800mm,
Coalesce(impor_800mm.tgl_bongkar_800mm_mulai, '-') AS tgl_bongkar_800mm_mulai,
Coalesce(impor_800mm.uraian_tipe_tiles_impor_800mm,
'-') AS uraian_tipe_tiles_impor_800mm,
Coalesce(impor_800mm.pieces_impor_800mm, '-') AS pieces_impor_800mm,
Coalesce(impor_800mm.pecah_in_pieces_impor_800mm,
'-') AS pecah_in_pieces_impor_800mm,
Coalesce(ekspor_800mm.tgl_peb_800mm, '-') AS tgl_peb_800mm,
Coalesce(ekspor_800mm.no_peb_800mm, '-') AS no_peb_800mm,
Coalesce(ekspor_800mm.tgl_muat_800mm_mulai, '-') AS tgl_muat_800mm_mulai,
Coalesce(ekspor_800mm.uraian_tipe_tiles_ekspor_800mm,
'-') AS uraian_tipe_tiles_ekspor_800mm,
Coalesce(ekspor_800mm.pieces_ekspor_800mm, '-') AS pieces_ekspor_800mm,
Coalesce(ekspor_800mm.sisa_pieces_800mm, '-') AS sisa_pieces_800mm
FROM ((impor_600mm
LEFT JOIN ekspor_600mm ON impor_600mm.impor_600mm_id =
ekspor_600mm.impor_600mm_id AND impor_600mm.inv_impor_cn_600mm =
ekspor_600mm.inv_impor_cn_600mm)
LEFT JOIN impor_800mm ON ekspor_600mm.inv_impor_cn_600mm =
impor_800mm.inv_impor_cn_800mm AND impor_600mm.no_pib_600mm =
impor_800mm.no_pib_800mm)
LEFT JOIN ekspor_800mm ON impor_800mm.impor_800mm_id =
ekspor_800mm.impor_800mm_id AND impor_800mm.inv_impor_cn_800mm =
ekspor_800mm.inv_impor_cn_800mm AND ekspor_600mm.no_peb_600mm =
ekspor_800mm.no_peb_800mm
WHERE (impor_800mm.pieces_impor_800mm <> 0) OR
(Coalesce(ekspor_600mm.no_peb_600mm, '-') = 0 AND
impor_600mm.pieces_impor_600mm <> 0) OR
(ekspor_600mm.pieces_ekspor_600mm <> 0) OR
(impor_600mm.pieces_impor_600mm <> 0 AND impor_600mm.auto_calculation_id = 2)
ORDER BY tgl_pib_600mm,
no_pib_600mm,
uraian_tipe_tiles_impor_600mm,
pieces_impor_600mm DESC
=========================================================================
it has all data inside the table.
the data impor 600mm table link to ekspor 600mm table and
the data impor 800mm table link to ekspor 800mm table and
all this 4 table link with the string field "inv_impor_cn_800mm"
when i display only 2 combined table impor 600mm and ekspor 600mm it display correctly and
when i display only 2 combined table impor 800mm and ekspor 800mm it also display correctly but
when i combine all 4 tables it display incorrectly. it has repeated data that suppose not to display the data. its like on other 2 tables impor 800mm and ekspor 800mm has contain data. and inside the 2 table impor 600mm dan ekspor 600mm dont have the data. but it filled the data with the value from the above row and repeating the data until the data on impor 800mm dan ekspor 800mm nothing to display.
so what i meant is that i would like to display the data properly when it is null or empty it display null or empty. not the repeated data from other rows.
i have also attached the mysql query when i displaying the data. (on above)
could some one with the kind heart give me some help. please. Thanks in advance