I have trhee table

1. PKS
   

2. IA
   

3. MOU
   

I want to combine the trhee of it into one table like this
Combined table

How can i do that ?, i already try left join but the result is not what i expected.

SELECT pks.satuan_kerja, count(pks.satuan_kerja) AS PKS, count(pelaksanaan_kerjasama.satuan_kerja) AS IA, count(mou.subsatker_mou) AS MOU
FROM pks
LEFT JOIN pelaksanaan_kerjasama
ON pks.satuan_kerja = pelaksanaan_kerjasama.satuan_kerja
LEFT JOIN mou
ON pks.satuan_kerja = mou.subsatker_mou
GROUP BY pks.satuan_kerja

CodePudding user response：

Use:

CREATE TABLE PKS_tbl          (
             Satker varchar(9),
               PKS int(4)     );
    
INSERT INTO PKS_tbl VALUES('FIK',2), ('FT',2);
   
CREATE TABLE IA_tbl          (
             Satker varchar(9),
             IA int(4)       );
 
INSERT INTO IA_tbl VALUES('FIK',1), ('FT',2);

CREATE TABLE MOU_tbl    (
            Satker varchar(9),
            MOU int(4)  );   

INSERT INTO MOU_tbl VALUES('FIK',3);


select p.Satker,
       PKS,
       i.IA,
       m.MOU
from PKS_tbl p 
inner join
(
select Satker, IA from IA_tbl ) as i on p.Satker=i.Satker
left join
(select Satker, MOU from MOU_tbl ) as m on p.Satker=m.Satker ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/111