Primbon MySQL-ku

Iseng-iseng aja pengen nulis diblog tapi gak ada ide yaudah aq tulis nih primbon sekalian buat belajar kalo nanti lupa. Sebenernya aq nda bakat jadi guru, susah nerangin ya jadinya gini deh. Semoga aja bermanfaat. Pasti akan terus diupdate berdasarkan pengalaman.

  1. Menampilkan RFQId, ProjectCode, ProjectName, EndUserName dan InstitutionName dimana RFQDetail sudah diapprove dan override score tidak ada yang bertipe veto (4)

    Select RFQ.RFQId, ProjectCode, ProjectName, EndUserName, InstitutionName FROM RFQ Left Outer Join RFQDetail ON RFQDetail.RFQId = RFQ.RFQId Left Outer Join EndUser ON EndUser.EndUserId = RFQ.EndUserId LEFT OUTER JOIN BidMaster ON BidMaster.RFQId = RFQ.RFQId WHERE (RFQ.Deleted = 0) AND (RFQDetail.Approve = 1) AND RFQ.RFQId not in (select bidmaster.RFQId from bidmaster where bidmaster.deleted = 0) AND RFQ.RFQId not in (select RFQOverrideScore.RFQId from RFQOverrideScore where OverrideType = 4) AND (ProjectName like ‘%smk%’ or ProjectCode like ‘%smk%’ or InstitutionName like ‘%smk%’);

  2. Hasilnya sebagai berikut :
    +——-+————-+————-+——————+—————–+
    | RFQId | ProjectCode | ProjectName | EndUserName      | InstitutionName |
    +——-+————-+————-+——————+—————–+
    |    11  | ABC            | SMK Tender  | SMKN 1 Purwosari | SMK Purwosari  |
    +——-+————-+————-+——————+—————–+
    1 row in set (0.08 sec)

  3. Select LTNumber yang diawali dengan LTP

    Select LtNumber FROM BidMaster WHERE (Deleted = 0) AND (Mid(LtNumber, 1, 3) = ‘LTP’) ORDER BY LtNumber ASC;

  4. Berikut ini hasilnya :
    +———-+
    | LtNumber    |
    +———-+
    | LTP9139     |
    +———-+
    1 row in set (0.01 sec)

  5. Menampilkan LtNumber, CountryName, EndUserName, ProjectValue dan BidStatusDescription dari bebrapa tabel meskipun field selain ProjectValue dan BidStatusDescription kosong sebab kedua field tersebut diinput pada proses paling awal (Pre RFQ). Jika LTNumber kosong field kosong berarti belum melalui proses Bidding dan seterusnya. Itulah sebabnya saya menggunakan LEFT OUTER JOIN.

    Select LtNumber, CountryName, EndUserName, ProjectValue, BidStatusDescription FROM RFQ Left Outer Join BidMaster ON BidMaster.RFQId = RFQ.RFQId Left Outer Join Country ON Country.CountryId = RFQ.CountryId Left Outer Join EndUser ON EndUser.EndUserId = RFQ.EndUserId Left Outer Join BidStatusType ON BidStatusType.BidStatusId = RFQ.BidStatusId WHERE (RFQ.Deleted = 0) AND (LTNumber like ‘%smkn 1 purwosari%’ or CountryName like ‘%smkn 1 purwosari%’ or EndUserName like ‘%smkn 1 purwosari%’);

  6. Berikut ini hasilnya :
    +———-+————-+——————+————–+———————-+
    | LtNumber    | CountryName   | EndUserName          | ProjectValue      | BidStatusDescription |
    +———-+————-+——————+————–+———————-+
    | LTP9139     | Indonesia        | SMKN 1 Purwosari     |           10         |
    RFQ                  |
    +———-+————-+——————+————–+———————-+
    1 row in set (0.00 sec)

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: