Subquery to get the desired output with correct joins

See more:

SQL

Hi - I have this subquery intend to get the output where the row count of entire query output should match with row count(13402) of subquery 'a' but am getting the row count as 13552.The intention is to get the details of all desired count for all the projects listed from the output of query 'a'(it should return null value if it's not matching with any of the other subqueries.

What I have tried:

SELECT  first_scan_issues.SOA_SECTOR,  
        first_scan_issues.SOA_REGION,  
        a.PROJECT,  
        a.SOLUTION,  
        first_scan_issues.CNT1,  
       first_scan_issues.CNT2,  
       first_file_scans.CNT7,  
       project_files_count.COUNT,  
        last_scan_issues.CNT3,  
        last_scan_issues.CNT4,  
        last_file_scans.CNT8,  
       prior_scan_issues.CNT5,  
        prior_scan_issues.CNT6,  
         prior_file_scans.CNT9,       
        CURRENT_DATE  
      FROM(select PROJECT,SOLUTION,FIRST_SCAN_ID,LAST_SCAN_ID,PRIOR_SCAN_ID from table1) a  
      LEFT OUTER JOIN  
      (  
      select  PROJECT,SCAN_ID,COUNT(ISSUE_ID) CNT1,  
        COUNT(DISTINCT FILE_ID) CNT2,SOA_SECTOR,SOA_REGION  
        from table2 group by PROJECT,SCAN_ID,SOA_SECTOR,SOA_REGION  
      )first_scan_issues on first_scan_issues.SCAN_ID = a.FIRST_SCAN_ID  
      LEFT OUTER JOIN  
      (   select  PROJECT,SCAN_ID,COUNT(ISSUE_ID) CNT3,  
        COUNT(DISTINCT FILE_ID) CNT4,SOA_SECTOR,SOA_REGION  
        from table2 group by PROJECT,SCAN_ID,SOA_SECTOR,SOA_REGION  
      )last_scan_issues ON last_scan_issues.SCAN_ID = a.LAST_SCAN_ID  
      LEFT OUTER JOIN  
      (  
       select  PROJECT,SCAN_ID,COUNT(ISSUE_ID) CNT5,  
        COUNT(DISTINCT FILE_ID) CNT6,SOA_SECTOR,SOA_REGION  
        from table2 group by PROJECT,SCAN_ID,SOA_SECTOR,SOA_REGION  
      )prior_scan_issues ON PRIOR_scan_issues.SCAN_ID = a.PRIOR_SCAN_ID  
      LEFT OUTER JOIN  
      (  
      select SCAN_ID,COUNT(DISTINCT FILE_ID) CNT7 from table2   
      group by SCAN_ID  
      )first_file_scans ON first_file_scans.SCAN_ID = a.FIRST_SCAN_ID  
      LEFT OUTER JOIN  
      (  
      select SCAN_ID,COUNT(DISTINCT FILE_ID) CNT8 from table2  
       group by SCAN_ID  
      )last_file_scans ON last_file_scans.SCAN_ID = a.LAST_SCAN_ID  
      LEFT OUTER JOIN  
      (  
      select SCAN_ID,COUNT(DISTINCT FILE_ID) CNT9 from table2  
       group by SCAN_ID  
      )prior_file_scans ON prior_file_scans.SCAN_ID = a.PRIOR_SCAN_ID  
       LEFT OUTER JOIN  
      (  
      SELECT  PROJECT,SOLUTION,COUNT(DISTINCT PATH) COUNT   
               FROM table2 GROUP BY PROJECT,SOLUTION) project_files_count  
          ON project_files_count.PROJECT = a.PROJECT);  
Table 1  
CREATE TABLE table1 (  
  PROJECT       VARCHAR2(256 CHAR) NOT NULL,  
  SOLUTION      VARCHAR2(256 CHAR),  
  FIRST_SCAN_ID NUMBER(10)         NOT NULL,  
  LAST_SCAN_ID  NUMBER(10)         NOT NULL,  
  PRIOR_SCAN_ID NUMBER(10)         NOT NULL,  
  CONSTRAINT pk_tmp_first_last_scan_ids_id PRIMARY KEY (PROJECT)  
);  
Table 2  
CREATE TABLE table2  
(  
  ISSUE_ID         NUMBER(10)  
    CONSTRAINT RPT_ISSUE_SUMMARY_PK  
    PRIMARY KEY,  
  SOA_SECTOR       VARCHAR2(128),  
  SOA_REGION       VARCHAR2(128),  
  USERNAME         VARCHAR2(128),  
  PROJECT          VARCHAR2(256),  
  SOLUTION          VARCHAR2(256),  
  PATH             VARCHAR2(2048),  
  TIME             DATE,  
  CATEGORY         VARCHAR2(512),  
  TITLE            VARCHAR2(512),  
  ISSUE_IMP        VARCHAR2(256),  
  CLICKED          NUMBER(3),  
  SCAN_ID          NUMBER(10),  
  SESSION_ID       NUMBER(10),  
  RULEPACK_RULE_ID NUMBER(10),  
  FILE_ID          NUMBER(10),  
  FILE_INFO_ID     NUMBER(10),  
  SCAN_YEAR_MON    VARCHAR2(7),  
  SCAN_YEAR        VARCHAR2(4),  
  SCAN_MONTH       VARCHAR2(2),  
  LOAD_TIME        DATE NOT NULL  
);