View topic - Tanya SQL :: Delphi Indonesia :: Pusat Informasi dan Komunitas Delphi Indonesia

stress? deadline kelewat?
bos ngamuk? pc meledak?
masuk sini !

Nikmati Delphi-ID dalam berbagai versi tampilan :
Basic View (LOW Bandwidth)
Default View (Full)
Mobile Edition

..:: onWebTools ::..

..:: onLastPosts ::..

jumlah komponen maksimal dalam sebuah project

(6)

by d4mnf1y32

on 21.May at 12:16

tanya grayscale to biner dengan scanline

(1)

by luckynvic

on 20.May at 20:44

tanya looping biner to matriks

(1)

by luckynvic

on 20.May at 20:10

sql error:acces denied for user....

(2)

by nasbms

on 19.May at 16:54

pemanggilan frame

(4)

by nasbms

on 19.May at 16:14

tanya AvLock

(0)

by azuriza

on 19.May at 11:24

Get TDBEdit Value Menggunakan LookUp Grid

(10)

by henry_sys

on 18.May at 11:27

menghitung record dalam perulangan

(4)

by idhiel

on 16.May at 13:53

tanya ascii

(4)

by mas_kofa

on 16.May at 12:11

RawPrint Untuk Delphi XE2

(0)

by adewijaya

on 15.May at 22:34

database error..

(4)

by idhiel

on 15.May at 15:57

bekasi, pt arila putra mahkota

(3)

by mas_kofa

on 15.May at 15:37

bagaimana cara membaca file *.doc dg delphi?

(0)

by ficky

on 15.May at 06:14

tanya biner to matriks

(13)

by diandewi

on 14.May at 20:30

..:: onLast Articles ::..

..:: New Download ::..

Pascal-id.Org

Feeds - Popular - Latest

RE: The Longue
8 months, 3 weeks ago

RE: belajar pascal
8 months, 3 weeks ago

RE: The Longue
8 months, 3 weeks ago

RE: belajar pascal
8 months, 3 weeks ago

RE: Perkenalan dan Absen
8 months, 3 weeks ago

RE: Alhamdulillah Buka Puasa Bersama
8 months, 4 weeks ago

RE: تَقَبَّلَ اللهُ مِنَّا وَمِنْكَ - Selamat Hari Raya Idul Fitri
8 months, 4 weeks ago

More...

Lintas Situs

Feeds - Popular - Latest

TSafeMe… BindMe
1 year, 2 months ago

PowerLogic SDK
1 year, 3 months ago

Getting Rid of the Middleman
1 month, 3 weeks ago

naming – What’s the use/meaning of the @ character in variable names in C#? – Stack Overflow
1 month, 3 weeks ago

Delphi On-Demand Webinars for Q1 2012
1 month, 3 weeks ago

Store Custom Data Into The Tree Node Of A Tree View
1 month, 3 weeks ago

Understanding and Writing Custom Enumerators and Iterators in Delphi
1 month, 3 weeks ago

More...

free web site stats and visitor tracking

Tanya SQL

Delphi Indonesia Forum Index » Pemrograman » Bedah Kasus

Author

Message

irmains

Location:

Post subject: Tanya SQL

Posted: 19/Jun/2009 13:57

onConfident

Joined: 18-Feb-2009
Posts: 25

Status: Offline

Tiba-tiba merasaa stuck n gak mudeng, mw tanya ya... Ada 2 tabel

-Tabel1-

idHasil|Nilai
81 100
82 80
83 99
84 89

-Tabel2-

idKriteria|Nilai
81 9
82 8
83 10
84 7
85 9
86 7
87 6
88 8
89 9
90 7

Yang mw ditanyakan, gmana ya caranya untuk menampilkan id yang ada di tabel2 tapi gak ada di tabel1, sehingga hasilnya nanti kaya gini

idKriteria|Nilai
85 9
86 7
87 6
88 8
89 9
90 7

Tiba-tiba merasa sulit bgt...

Matur nuwun sanget nggih Kakak-Kakak...

irmains

Location:

Post subject: RE: Tanya SQL

Posted: 19/Jun/2009 14:21

onConfident

Joined: 18-Feb-2009
Posts: 25

Status: Offline

Maaf Maaf,, gak jadi tanya kakak, udah bisa \(^_^)/

Ternyata cuma

s elect * from table2
w here idKriteria not in (s elect idHasil from table1);

Matur nuwun sanget nggih Kakak-Kakak...

elva_ivana

Location:

Post subject: RE: Tanya SQL

Posted: 19/Jun/2009 14:26

onProfessional

Joined: 22-Jan-2008
Posts: 597

Manz

Location: C.h.i.l.l.e -g- O.n.e

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 24/Jun/2009 15:36

onElite

Joined: 12-Sep-2006
Posts: 1161
Location: C.h.i.l.l.e -g- O.n.e
Status: Offline

Nah, dilanjut

Code:

Tabel karyawan:

NIP   | Nama

------------------------

001   | Udin tea

002   | Budi Kahandap

003   | Andi Kararangge

004   | Cecep Gorbacep

005   | Ono Ontohod

Tabel master_absen:

tipe_absen | deskripsi

----------------------

AL         | ALPA

SK         | SAKIT

CT         | CUTI

Tabel absen:

NIP  |     TGL    | tipe_absen

------------------------------

001  | 01/07/2009 | AL

001  | 02/07/2009 | AL

002  | 05/07/2009 | CT

004  | 05/07/2009 | CT

002  | 06/07/2009 | SK

003  | 07/07/2009 | SK

003  | 08/07/2009 | SK

003  | 09/07/2009 | SK

003  | 10/07/2009 | SK

005  | 11/07/2009 | AL

005  | 12/07/2009 | AL

005  | 13/07/2009 | AL

005  | 14/07/2009 | AL

Hasil yang di-ingin-kan:

Code:

NIP | NAMA            | AL | CT | SK

------------------------------------

| Udin tea        | 2  | 0  | 0

| Budi Kahandap   | 0  | 1  | 0

| Andi Kararangge | 0  | 0  | 4

| Cecep Gorbacep  | 0  | 1  | 0

| Ono Ontohod     | 4  | 0  | 0

Tapi kalau data master_absen ditambah menjadi :

Code:

tipe_absen | deskripsi

--------------------------

AL         | ALPA

SK         | SAKIT

CT         | CUTI

IZ         | IZIN PULANG

kemudian data absen jadi : 

NIP  |     TGL    | tipe_absen

------------------------------

001  | 01/07/2009 | AL

001  | 02/07/2009 | AL

002  | 05/07/2009 | CT

004  | 05/07/2009 | CT

002  | 06/07/2009 | SK

003  | 07/07/2009 | SK

003  | 08/07/2009 | SK

003  | 09/07/2009 | SK

003  | 10/07/2009 | SK

005  | 11/07/2009 | AL

005  | 12/07/2009 | AL

005  | 13/07/2009 | AL

005  | 14/07/2009 | AL

003  | 20/07/2009 | IZ

004  | 21/07/2009 | IZ

hasilnya pengen jadi :

Code:

NIP | NAMA            | AL | CT | SK | IZ

-----------------------------------------

| Udin tea        | 2  | 0  | 0  | 0

| Budi Kahandap   | 0  | 1  | 0  | 0

| Andi Kararangge | 0  | 0  | 4  | 1

| Cecep Gorbacep  | 0  | 1  | 0  | 1

| Ono Ontohod     | 4  | 0  | 0  | 0

Nah, pertanyaannya :

Gimana caranya pengen pake single SQL Statements Question

tanpa pake luping lupingan nu lieur on tohod ... Mr. Green

Hatur Tengkiu ...

Man'z tea geuningan Mr. Green

_________________
..:: Everything what i want to write It! ::..
http://manztiara.wordpress.com/ [But, too bad to become bloggers ... hiks...] updated little

muktaf

Location:

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 24/Jun/2009 16:35

onKnowledgeable

Joined: 12-Jan-2007
Posts: 204

itu namanya row to column ... coba pake if atau case di sql untuk bikin row to column..

Manz

Location: C.h.i.l.l.e -g- O.n.e

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 24/Jun/2009 16:36

onElite

Joined: 12-Sep-2006
Posts: 1161
Location: C.h.i.l.l.e -g- O.n.e
Status: Offline

@muktaf ... we need the code ... not the statement only ... hehehehehehe Mr. Green

_________________
..:: Everything what i want to write It! ::..
http://manztiara.wordpress.com/ [But, too bad to become bloggers ... hiks...] updated little

muktaf

Location:

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 24/Jun/2009 16:39

onKnowledgeable

Joined: 12-Jan-2007
Posts: 204

select sum(IF(tipe_absen='AL')) as AL,sum(IF(tipe_absen='CT')) as CT,sum(IF(tipe_absen='SK')) as SK,sum(IF(tipe_absen='IZ')) as IZ form namatable group by nip

Mudah mudahan membantu

nurez

Location:

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 25/Jun/2009 09:30

onUber-Skilled

Joined: 08-Jul-2005
Posts: 379

Status: Offline

"dijoinin" aja hatuh kangg...
tp aq jg blom terfikirkan untuk mencobanya... hehehe...maff... Smile

_________________
..seindah "teh manis anget ...."makasih"...
[location : cinere-h.nawy] [-keep coding on-]

elva_ivana

Location:

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 25/Jun/2009 09:51

onProfessional

Joined: 22-Jan-2008
Posts: 597

kang Manz coba di http://tampanteddy.blogspot.com/2009/06/tanya-sql.html

sikuper85

Location:

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 25/Jun/2009 10:21

onSkilled

Joined: 13-Jun-2009
Posts: 65

Status: Offline

hhmmn, yg diharapkan kang Manz tu bukan hal yg simpel (sesimpel itu klo kata kang Manz-nya mah, kali). (abis liat2 blog-nya kang Manz, widiw serem banged).

yg diharapkan kang Manz, sepertinya, satu statement sql (oriented bgt sama yg 1 ini yawh), yg bisa menghasilkan dataset dengan jumlah kolom yg dinamis sesuai dengan isi data dari tabel tertentu.
(memungkinkan ga yawh... [untuk saat ini maksudnya])

klo yg standar seperti yg diatas (ga dinamis), ga pake view juga bisa deh kayanya, tinggal pake kode yg mirip dari pak muktaf.

code : sql
...
Sum(IIf(tipe_absen='AL',1,0)) AS AL, 
Sum(IIf(tipe_absen='CT',1,0)) AS CT, 
Sum(IIf(tipe_absen='SK',1,0)) AS SK, 
...
Parsed in 0.003 seconds, using GeSHi

lho kok elipsis, (hemat energi... ^_^)

CMIIW.

Manz

Location: C.h.i.l.l.e -g- O.n.e

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 25/Jun/2009 10:51

onElite

Joined: 12-Sep-2006
Posts: 1161
Location: C.h.i.l.l.e -g- O.n.e
Status: Offline

@Nurez ... bisa dicobain dan difikirkan
@elva_ivana ... wah ... kan datanya di master absen itu dynamic ...
@sikuper85 ... ini memungkinkan menghasilkan dataset dynamic column ... lanjutnya ... no comment ahhh .. Mr. Green

Walahh ... mana neh yang jago - jago sqlnya lagi ... koq blon nonghol euy Mr. Green

Man'z tea geuningan

_________________
..:: Everything what i want to write It! ::..
http://manztiara.wordpress.com/ [But, too bad to become bloggers ... hiks...] updated little

yayaretina

Location: Jakarta

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 25/Jun/2009 11:21

onElite

Joined: 07-Oct-2006
Posts: 1180
Location: Jakarta

teng tong..
@Manz kayaknya lagi need some help nih..
ehmmm.. mbantu doa aja dech.. Mr. Green

elva_ivana

Location:

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 25/Jun/2009 12:48

onProfessional

Joined: 22-Jan-2008
Posts: 597

setelah wara-wiri ...

haidarfaz

Location:

Post subject: RE: Tanya SQL ( Maniak SQL )

Posted: 25/Jun/2009 22:52

onConfident

Joined: 11-Jun-2009
Posts: 31

Status: Offline

itu kalo di produknya microsoft disebut crosstab. pake PIVOT tabel. Mestinya access dan MSSQL bisa.
Firebird? kayaknya belum diimplentasikan. tp sdh diusulkan.
MySQL? ga ngerti...

Jump to:

All times are GMT - 12 Hours

Delphi Indonesia Forum Index » Pemrograman » Bedah Kasus

-=-		. ..... \| messages \|
RSS		Direkomendasikan menggunakan brower Opera/Chrome/Firefox. Resolusi minimal 1024x768 \| Powered by ____ \| KiOSS.Com \| Switch to Default View \|