CODEWITHSUNDEEP
phpsql
Bossby
Bossby

Reputation: 49

SQL 3 Type of conditions

I've a proble to generating a results from my sql. Well my sql is for searching data with 3 conditions.

  1. With transaction number
  2. By date
  3. Transcation number and date

Here is my query so far :->

select a.totalpembelian,a.idpembelian,notransaksibeli,a.tanggal,c.namabarang,b.totalbarang,c.hargabarang,b.totalharga,d.namasupplier from pembelian_header a 
join pembelian_detail b on 
    a.idpembelian = b.idpembelian left join 
    barang c on b.idbarang = c.idbarang left join supplier d on b.idsupplier=d.idsupplier 
    where (notransaksibeli like '%$notrans%' and a.tanggal='$tanggal')
    or (notransaksibeli like '%$notrans%') or (a.tanggal='$tanggal')
group by totalpembelian

I cannot generate the right result if I insert the transaction number and date.

Upvotes: 0

Views: 29

Answers (2)

Scott
Scott

Reputation: 3732

So logically, your three conditions are actually only 2 conditions. if you have:

  1. A or
  2. B or
  3. A and B

then #3 will always be true if #1 or #2 are true. So you can simplify your query a bit there. But I'm afraid this is only a partial answer because I don't know your actual data or what result you're getting. However you may find simplifying your query helpful to understanding what it's actually doing.

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

Since you're matching on either, but want to give preference where there's a match on both, you need your criteria to require either both variables be populated, or only one of them be populated, you can do this via = '' or IS NULL depending on how your unset variables are set up:

    where (notransaksibeli like '%$notrans%' and a.tanggal='$tanggal')
    or (notransaksibeli like '%$notrans%' AND $tanggal = '') 
    or (a.tanggal='$tanggal' AND $notrans = '')
group by totalpembelian

Or:

    where (notransaksibeli like '%$notrans%' and a.tanggal='$tanggal')
    or (notransaksibeli like '%$notrans%' AND $tanggal IS NULL) 
    or (a.tanggal='$tanggal' AND $notrans IS NULL)
group by totalpembelian

Upvotes: 0

Related Questions