[SOLVED] Join four tables and return only records from fourth table

Join four tables and return only records from fourth table

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'

Solution

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'