amin, ko nak output dia mcmana? xpaham
mm,mdt,pan,bst,qss semua tu table ke field?

amin, ko nak output dia mcmana? xpaham Big smile mm,mdt,pan,bst,qss semua tu table ke field?

mm,mdt,pan tu semuanya table

plan aku nak buat join table semuanya
tapi ada masalah dari segi fid

guna using
contoh

fom m join x using (ic) join d using (ic)

amin ni mysql bukan php.kalau orm consider as php la.

boleh dak hampa bagi dalam bentuk visual atau berstruktur jadual-jadual sedia ada dan jadual yang hampa impikan. jadi senang la sikit kami nak faham input dan outputnya

JADUAL MM

JADUAL MDT

JADUAL PAN

JADUAL BST

ok ini adalah contoh jadual. dalam sistem aku jadual ini terletak di 4 pangkalan data mm,mdt,pan dan bst.
jadi boss aku kalau boleh nak join semua jadual ni jadi satu jadual sahaja.
masalah satu setiap jadual ada simpan data2 berasingan. yang sama antara 4 jadual tersebut adalah
fid,nama,respon dan pegawai

jadi boss aku nak tahu prestasi kerja setiap orang ikut kes masing-masing
dalam laporan dia nak macam ni

jadi kat sini boss aku dapat tahu yang pegawai bernama abu bakar masih belum dapat kes
untuk survey mdt. terus dia boleh call handset dia dan tanya kes dia dah selesai ker belum

First Union semua table then buat view.Buat sum if based of view tersebut.

guna je la php.

select sum(if(count(pegawai)),xx,x from (select survay,pegawai  from table1 union select survay,pegawai from table 2) as info where 1.

Konsep lebih kurang cam ni la.

kalau pakai sql, sebaris saja. tapi, nak menghasilkan sebaris tu... he he he

kami cadangkan buat satu jadual mengandungi senarai nama pegawai

<table border="1">
	<tr>
		<th>Pegawai</th>
		<th>MM</th>
		<th>MDT</th>
		<th>PAN</th>
		<th>BST</th>
	</tr>
	<?php 
	$result = mysql_query("SELECT * FROM pegawai");
	while ($row = mysql_fetch_array($result)) {
		$nama = $row['nama'];
		$mm = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS bil FROM mm WHERE pegawai = '$nama'"));
		$mdt = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS bil FROM mdt WHERE pegawai = '$nama'"));
		$pan = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS bil FROM pan WHERE pegawai = '$nama'"));
		$bst = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS bil FROM bst WHERE pegawai = '$nama'"));
		echo "<tr align=\"center\"><td align=\"left\">$nama</td>";
		echo "<td>{$mm['bil']}</td>";
		echo "<td>{$mdt['bil']}</td>";
		echo "<td>{$pan['bil']}</td>";
		echo "<td>{$bst['bil']}</td></tr>";
	}
	?>
</table>

amin, table2 ko ni ade relation yg sesuai x antara 1 same lain?xkn nama pegawai kot relation dia?
kalau based on nama pegawai, try cmni jadi x? aku x test pn ni, br buat logik je..ko test sndiri la

contoh utk nama pegawai ni kalau unique je la..

sy_hadri wrote:

amin, table2 ko ni ade relation yg sesuai x antara 1 same lain?xkn nama pegawai kot relation dia? kalau based on nama pegawai, try cmni jadi x? aku x test pn ni, br buat logik je..ko test sndiri la

contoh utk nama pegawai ni kalau unique je la..

SELECT a.pegawai,COUNT(a.fid)AS MM,COUNT(b.fid) AS MDT,COUNT(c.fid) AS PAN,COUNT(d.fid) AS BST FROM mm a LEFT JOIN mdt b ON b.pegawai=a.pegawai LEFT JOIN pan c ON c.pegawai=b.pegawai LEFT JOIN bst d ON d.pegawai=c.pegawai GROUP BY a.pegawai;

tak yer juga, join ikut nama pegawai, tak sangka ko ini seorang yang bijak sy_hadri.
cuma tak semua pegawai pegang semua kes. tapi tak apa, aku cuba join kejap

bijak ke kurang bijak ke xbijak ke semua tu kurniaanNya..jgn lupa
haa,kalau xsemua pegawai ko cuba la join biase tanpa left join, dpt la pegawai yg ade kes je..
xpun ko dptkn dlu senarai pegawai yg ade kes..teori je,xpasti jd x..hehe

boleh try ni. tak tau la performance camana

1. create view
CREATE VIEW combine_table AS
SELECT * FROM bst
UNION
SELECT * FROM mdt
UNION
SELECT * FROM mm
UNION
SELECT * FROM pan

2. run sql
select `pegawai` ,
(select count(nama) from mm where pegawai = combine_table.pegawai) as mm ,
(select count(nama) from mdt where pegawai = combine_table.pegawai) as mdt ,
(select count(nama) from pan where pegawai = combine_table.pegawai) as pan ,
(select count(nama) from bst where pegawai = combine_table.pegawai) as bst
from combine_table group by ( `pegawai`)

atau

select `pegawai` ,
sum(case when survey = 'mm' THEN 1 ELSE 0 END) as mm,
sum(case when survey = 'mdt' THEN 1 ELSE 0 END) as mdt,
sum(case when survey = 'pan' THEN 1 ELSE 0 END) as pan,
sum(case when survey = 'bst' THEN 1 ELSE 0 END) as bst
from combine_table group by ( `pegawai`)

3. kalau malas buat view
select `pegawai` ,
sum(case when survey = 'mm' THEN 1 ELSE 0 END) as mm,
sum(case when survey = 'mdt' THEN 1 ELSE 0 END) as mdt,
sum(case when survey = 'pan' THEN 1 ELSE 0 END) as pan,
sum(case when survey = 'bst' THEN 1 ELSE 0 END) as bst
from (SELECT * FROM bst
UNION
SELECT * FROM mdt
UNION
SELECT * FROM mm
UNION
SELECT * FROM pan) as combine_table group by ( `pegawai`)