Dewangga Shinnosuke Dewangga Shinnosuke - 6 months ago 19
MySQL Question

show record in between field in mysql not using sub query

i want the field nilai_kredit to top at between filed nilai_kredit, if the acct_no is same, but i don't want to use sub query because is slowly

SELECT
a.acct_no,
IF (a.kode = 'D', SUM(a.nilai), 0) AS nilai_debet,
IF (a.kode = 'K', SUM(a.nilai), 0) AS nilai_kredit
FROM
tr2015 a
INNER JOIN acct2015 b
ON a.acct_no = b.acct_no AND b.sub='AP'
WHERE a.tgl LIKE '2015-01%'AND a.hps <> '*'
GROUP BY a.acct_no,a.kode


this is my result

Answer
SELECT
  a.acct_no,
  SUM(IF(a.kode = 'D', a.nilai, 0)) AS nilai_debet,
  SUM(IF(a.kode = 'K', a.nilai, 0)) AS nilai_kredit 
FROM
  tr2015 a
INNER JOIN acct2015 b
  ON a.acct_no = b.acct_no AND b.sub='AP'
WHERE a.tgl LIKE '2015-01%'AND a.hps <> '*'
GROUP BY a.acct_no