--- In [EMAIL PROTECTED], Fikri Ms <[EMAIL PROTECTED]> wrote:
> Hello delphindo,
>
> Saya punya tabel M_Regu, M_Pelanggan, Konseling
> M_Regu :
> KD_REGU NM_REGU
> 001 ADI
> 002 ANTO
> 003 ALI
>
>
> M_PELANGGAN :
> NO_TELP NM_PEL REGU
> 8854321 BUDI 002
> 8812345 BADRUN 002
> 7712345 BONDAN 003
> 7754321 BIBI 001
> 6612345 CICI 001
> 6654321 CICA 003
>
> KONSELING
> TGL NO_TELP
> 01/01/04 8812345
> 01/01/04 7712345
> 01/01/04 6654321
> 01/01/04 4499999 -> telp nyasar
>
> Mohon bantuannya utk menampilkan :
> 1. Data yg Sudah Menelpon
> TGL NO_TELP NM_PELANGGAN NM_REGU
> 01/01/04 8812345 BADRUN ANTO
> 01/01/04 7712345 BONDAN ALI
> 01/01/04 6654321 CICA ALI
>
> 2. Pelanggan yg belum menelpon
> TGL NO_TELP NM_PELANGGAN NM_REGU
> 01/01/04 8854321 BUDI ANTO
> 01/01/04 7754321 BIBI ADI
> 01/01/04 6612345 CICI ADI
>
> 3. Data Telepon Nyasar (Gak ada di master)
> TGL NO_TELP
> 01/01/04 4499999
>
> 4. Regu yang belum ditelepon
> KD_REGU NM_REGU
> 001 ADI
>
> Saya sudah mencoba, mohon dibetulkan kalo salah :
> 1. select A.TGL,A.NO_TELP,B.NM_PELANGGAN,C.NM_REGU
> from (KONSELING A inner join M_PELANGGAN B
> on A.NO_TELP=B.NO_TELP) inner join M_REGU C
> on B.REGU=C.KD_REGU
>
> no 2,3,4 ???
> Terima kasih ...
>
> --
> Best regards,
> Fikri mailto:[EMAIL PROTECTED]
I coba
1)
2)
SELECT
A.TGL,
B.NO_TELP,
B.NM_PEL,
C.NM_REGU
FROM KONSELING A
RIGHT join M_PELANGGAN B
on A.NO_TELP=B.NO_TELP
inner join M_REGU C
on B.REGU=C.KD_REGU
WHERE A.TGL IS NULL
nb:
field tanggal seharusnya kan kosong,
karena belum pernah telepon
3)
SELECT
A.TGL,
A.NO_TELP
FROM KONSELING A
LEFT JOIN M_PELANGGAN B
ON A.NO_TELP = B.NO_TELP
WHERE B.NO_TELP IS NULL
4)
SELECT
A.NM_REGU,
SUM(IF(C.NO_TELP IS NULL, 0, 1)) AS CEK
FROM M_REGU A
LEFT JOIN M_PELANGGAN B
ON A.KD_REGU = B.REGU
LEFT JOIN KONSELING C
ON B.NO_TELP = C.NO_TELP
GROUP BY NM_REGU
HAVING CEK = 0
nb:
penambahan kolom cek hanya untuk
memastikan bahwa regu sama sekali belum
ditelepon
bisa dimanipulasi untuk tidak ditampilkan
ini untuk mencegah ANTO ikut ditampilkan
mungkin rekan yg lain bisa membantu
CMIIW
salam,
p4ydj0
Berlangganan: [EMAIL PROTECTED]
Stop Berlangganan: [EMAIL PROTECTED]
Keluhan Milis(Unbouncing,spam,dll): [EMAIL PROTECTED]
| Yahoo! Groups Sponsor | |
|
|
Yahoo! Groups Links
- To visit your group on the web, go to:
http://groups.yahoo.com/group/Delphindo/
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
