akiong akiong - 20 days ago 6
MySQL Question

get all data from a query select in select union mysql

i really confuse to get a full record from my query.

i already try the best, but i still cannot get what i want.

i am using mysql 5.5, i really dont know what to do again.

i will share my database.sql because my query join more than 5 table.

database_schema.sql

and this is my query where i tried before.

select id_akun, kode_akun, nama_akun, SUM(saldo_awal) as 'SaldoAwal', SUM(debet) as 'Debet', SUM(kredit) as 'Kredit', (SUM(saldo_awal) + SUM(debet) - SUM(kredit)) as 'SaldoAkhir' from (
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) as saldo_awal, b.amount as 'debet', 0 as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c
where a.id=b.account_id AND b.transaksi_kas_id=c.id
AND dt='D' AND c.trx_type IN ('1', '2')
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c
where a.id=b.account_id AND b.transaksi_kas_id=c.id
AND dt='K' AND c.trx_type IN ('1', '2')
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c, kas d
where a.id=b.account_id AND b.transaksi_kas_id=c.id AND c.kas_id=d.id
AND dt='D' AND c.trx_type IN ('3', '4') AND d.jenis != '3'
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c, kas d
where a.id=b.account_id AND b.transaksi_kas_id=c.id AND c.kas_id=d.id
AND dt='K' AND c.trx_type IN ('3', '4') AND d.jenis != '3'
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c
where a.id=b.account_id AND b.transaksi_kas_id=c.id
AND dt='D' AND c.trx_type IN ('5', '6')
UNION
select a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
from account a, transaksi_kas_detail b, transaksi_kas c
where a.id=b.account_id AND b.transaksi_kas_id=c.id
AND dt='K' AND c.trx_type IN ('5', '6')
) fff WHERE group_account_id = '15'
GROUP BY id_akun, kode_akun, nama_akun
ORDER BY kode_akun asc


after i run this query..

i will get this result.

enter image description here

i just get 2 record, i need to get all data by
ORDER BY kode_akun
where i get from table
account


if table account have 10 rows, then my query must show 10 rows.

maybe someone can help me

for example like this..

enter image description here

Answer

From you further descriptions I think what you need is to LEFT JOIN the transaksi_kas_detail AND transaksi_kas tables (and the kas table when used).

Note that you example data has the details broken down further, and not sure how you are getting that extra data.

But something like this

SELECTid_akun, kode_akun, nama_akun, SUM(saldo_awal) as 'SaldoAwal', SUM(debet) as 'Debet', SUM(kredit) as 'Kredit', (SUM(saldo_awal) + SUM(debet) - SUM(kredit)) as 'SaldoAkhir' 
from 
(
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) as saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('1', '2')
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('1', '2')
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('3', '4')
    LEFT OUTER JOIN kas d ON c.kas_id = d.id AND d.jenis != '3'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('3', '4')
    LEFT OUTER JOIN kas d ON c.kas_id = d.id AND d.jenis != '3'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('5', '6')
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('5', '6')
) fff 
WHERE group_account_id = '15' 
GROUP BY id_akun, kode_akun, nama_akun
ORDER BY kode_akun asc

EDIT - checking the dates in the ON clauses:-

SELECTid_akun, kode_akun, nama_akun, SUM(saldo_awal) as 'SaldoAwal', SUM(debet) as 'Debet', SUM(kredit) as 'Kredit', (SUM(saldo_awal) + SUM(debet) - SUM(kredit)) as 'SaldoAkhir' 
from 
(
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) as saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('1', '2') AND c.trx_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('1', '2') AND c.trx_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('3', '4') AND c.trx_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    LEFT OUTER JOIN kas d ON c.kas_id = d.id AND d.jenis != '3'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', c.trx_date, (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('3', '4') AND c.trx_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    LEFT OUTER JOIN kas d ON c.kas_id = d.id AND d.jenis != '3'
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, b.amount as 'debet', 0 as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'D' AND b.cek_due_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('5', '6')
    UNION ALL
    SELECT a.group_account_id, a.id as 'id_akun', a.code as 'kode_akun', a.name as 'nama_akun', b.cek_due_date as 'trx_date', (saldo_awal_debet - saldo_awal_kredit) saldo_awal, 0 as 'debet', b.amount as 'kredit'
    FROM account a 
    LEFT OUTER JOIN transaksi_kas_detail b ON a.id = b.account_id AND b.dt = 'K' AND b.cek_due_date BETWEEN '2016-01-01 00:00:00' AND '2016-11-16 00:00:00'
    LEFT OUTER JOIN transaksi_kas c ON b.transaksi_kas_id = c.id AND c.trx_type IN ('5', '6')
) fff 
WHERE group_account_id = '15' 
GROUP BY id_akun, kode_akun, nama_akun
ORDER BY kode_akun asc