Join four tables and return only records from fourth table

Question

In my query, I have 4 tables (A,B,C and D) and I am joining them using the below provided conditions. I need to return records (objectid) that are present only in Table D. The objectid in Table D are all unique.

Table D has only 1 million records, but when I run the below query, it gives me 5 million records. Kindly help me in fixing the query.

select D.*
from Table_A A

join Table_B B on B.documentid = a.documentid

join Table_C C on B.objectid = C.formid and 
  B.instance_seq = C.instance_seq
and 
  C.src_exp = date '9999-12-31'

join Table_D D on C.sourceid = D.objectid
and 
  C.instance_seq = D.instance_seq
and 
  D.src_exp = date '9999-12-31'

where A.src_exp = date '9999-12-31' and A.form = 'Tentative'

Answer 1

Select from D only and then check that you have matching data that EXISTS in the other tables with the appropriate filter conditions:

SELECT *

FROM   Table_D D
WHERE  EXISTS(
         SELECT 1
         FROM   Table_A A

                INNER JOIN Table_B B
                ON B.documentid = a.documentid

                INNER JOIN Table_C C
                ON     B.objectid = C.formid
                   AND B.instance_seq = C.instance_seq

         WHERE  C.src_exp = date '9999-12-31'

         AND    C.sourceid = D.objectid

         AND    C.instance_seq = D.instance_seq

         AND    A.src_exp = date '9999-12-31'
         AND    A.form = 'Tentative'
      )
AND   D.src_exp = date '9999-12-31'