CODEWITHSUNDEEP
mysqljoin
user2430933
user2430933

Reputation: 39

How to join 5 tables MYSQL with specific condition

i have 5 tables below

tb_satker

kdsatker
1
2
3

tb_akun

akun | code
A    | 1
B    | 2
C    | 3

tb_simponi

kdsatker | akun | jumlah
1        | A    | 100
1        | B    | 200

tb_span

kdsatker | akun | jumlah
1        | A    | 1
1        | B    | 2
1        | C    | 3

tb_upt

kdsatker | akun | jumlah
1        | A    | 10
1        | B    | 20
1        | C    | 30

What i need is output to something like this

kdsatker | akun | simponi | span | upt
1        | A    | 100     | 1    | 10
1        | B    | 200     | 2    | 20
1        | C    | 0       | 3    | 30
2        | 0    | 0       | 0    | 0
3        | 0    | 0       | 0    | 0

I've try using sql fiddle but the result is not right (http://sqlfiddle.com/#!9/e83ce7/10)

SELECT tb_satker.kdsatker,tb_akun.akun,tb_simponi2.simponi,tb_span2.span,tb_upt2.upt
FROM tb_satker
LEFT JOIN (SELECT kdsatker,akun,sum(tb_simponi.jumlah) as simponi FROM tb_simponi GROUP BY kdsatker,akun)
as tb_simponi2 ON tb_simponi2.kdsatker=tb_satker.kdsatker
LEFT JOIN (SELECT kdsatker,akun,sum(tb_span.jumlah) as span FROM tb_span GROUP BY kdsatker,akun)
as tb_span2 ON tb_span2.kdsatker=tb_satker.kdsatker
LEFT JOIN (SELECT kdsatker,akun,sum(tb_upt.jumlah) as upt FROM tb_upt GROUP BY kdsatker,akun)
as tb_upt2 ON tb_upt2.kdsatker=tb_satker.kdsatker
LEFT JOIN 
tb_akun ON tb_akun.akun=tb_simponi2.akun AND
tb_akun.akun=tb_span2.akun AND
tb_akun.akun=tb_upt2.akun
GROUP BY tb_satker.kdsatker,tb_akun.akun

Can anybody help me with the right idea?? Many Thanks

Upvotes: 0

Views: 34

Answers (1)

DRapp
DRapp

Reputation: 48179

Your inner queries are all based on a two-part group by two parts... kdsatker and akun. Since it is possible for any of the tb_simponi, tb_span, tb_upt can have any combination, I would pre-union all possible combinations of those FIRST. THEN, Join to the summary results. and FINALLY get the akun and kdsatker components. Something like...

SELECT 
        tb_satker.kdsatker,
        coalesce( tb_akun.akun, 0 ) akun,
        coalesce( tb_simponi2.simponi, 0 ) simponi,
        coalesce( tb_span2.span, 0 ) span,
        coalesce( tb_upt2.upt, 0 ) upt
    FROM 
        tb_satker
            LEFT JOIN
            ( SELECT distinct kdsatker, akun FROM tb_simponi 
                UNION
                  SELECT kdsatker, akun FROM tb_span 
                UNION
                  SELECT kdsatker, akun FROM tb_upt 
             ) AllKdAkun
                on tb_satker.kdsatker = AllKdAkun.kdsatker

                LEFT JOIN tb_akun
                    on AllKdAkun.akun = tb_akun.akun

                LEFT JOIN 
                (SELECT kdsatker, akun, sum(tb_simponi.jumlah) as simponi 
                    FROM tb_simponi 
                    GROUP BY kdsatker, akun ) as tb_simponi2 
                    ON AllKdAkun.kdsatker = tb_simponi2.kdsatker
                    AND AllKdAkun.Akun = tb_simponi2.akun

                LEFT JOIN 
                (SELECT kdsatker, akun, sum(tb_span.jumlah) as span 
                    FROM tb_span 
                    GROUP BY kdsatker, akun ) as tb_span2 
                    ON AllKdAkun.kdsatker = tb_span2.kdsatker
                    AND AllKdAkun.akun = tb_span2.akun

                LEFT JOIN 
                (SELECT kdsatker, akun, sum(tb_upt.jumlah) as upt 
                    FROM tb_upt 
                    GROUP BY kdsatker, akun) as tb_upt2 
                    ON AllKdAkun.kdsatker = tb_upt2.kdsatker
                    AND AllKdAkun.akun = tb_upt2.akun

No final group by at the outer level.

First level is your tb_satker. There may (or not) be a record in any of the subsidiary tables, so that is first.

Second level is a distinct list of every kdsatker, akun in ANY of the other 3 tables. So now you can LEFT JOIN kdsatker thus keeping every record including 1, 2, 3 even though 2 & 3 have no records.

From the second level, you can now left-join to the tb_akun table which in this case, only kdsatker is the only one with records for any given akun.

Finally joining the pre-union list of all combinations grouped by kdsatker, akun are able to be matched for their final summary into the final list.

Hope each step makes sense to what you appear to be trying for.

Upvotes: 1

Related Questions