Query SQL Server for JOIN and WHERE

November 16, 2022

I have problem with my sql query.
I want to join 2 table, with condition using where ‘id_pegawai’ and ‘status’ = 1.

Table1: SELECT * FROM form_pertambahan_anak

Table2: SELECT * FROM peg_dtl_anak WHERE id_pegawai = '0000000360'

My Query: SELECT fpa.id_pegawai, fpa.tanggal_pengusulan, fpa.id_anak, pda.id, pda.nama_anak, fpa.deleted_at, fpa.status FROM form_pertambahan_anak fpa INNER JOIN peg_dtl_anak pda ON pda.id_pegawai = fpa.id_pegawai WHERE fpa.id_pegawai = '0000000360' AND fpa.deleted_at IS NULL AND fpa.status = '1'

Why return 2 data? 1 expect just showing 1 data from form_pertambahan_anak with status = 1.

>Solution :

As far as I can see, you need to add an id_anak equality condition to the query:

SELECT fpa.id_pegawai
     , fpa.tanggal_pengusulan
     , fpa.id_anak
     , pda.id
     , pda.nama_anak
     , fpa.deleted_at
     , fpa.status
  FROM form_pertambahan_anak fpa
  INNER JOIN peg_dtl_anak pda ON pda.id_pegawai = fpa.id_pegawai
                             AND pda.id = fpa.id_anak --here
WHERE fpa.id_pegawai = '0000000360'
  AND fpa.deleted_at IS NULL
  AND fpa.status = '1'