I am stuck on the final part of the query, I know that this thing is easy, but I am out of ideas now, so sorry for this.
I have prepared a query like this:
SELECT A.projectName as PARENT, (select COUNT(*) from PSPROJECTITEM WHERE PROJECTNAME = A.PROJECTNAME) parentprojecount, B.ProjectName as CHILD, (select COUNT(*) from PSPROJECTITEM WHERE PROJECTNAME = B.PROJECTNAME) CHILDPROJECT, COUNT(*) AS SIMILARCOUNT from psprojectitem a INNER JOIN psProjectItem B ON a.objecttype = b.objecttype AND a.objectid1 =b.objectid1 AND a.objectvalue1 = b.objectvalue1 AND a.objectid2 = b.objectid2 AND a.objectvalue2 = b.objectvalue2 AND a.objectid3 = b.objectid3 AND a.objectvalue3 = b.objectvalue3 AND a.objectid4 = b.objectid4 AND a.objectvalue4 = b.objectvalue4 WHERE A.projectname in (SELECT ProjectName from psProjectDefn WHERE lastupdoprid <> 'pplsoft') AND a.projectname <> B.projectName and A.PROJECTNAME = 'AAAA_JOB_KJ' group by A.PROJECTNAME,B.PROJECTNAME ORDER BY B.PROJECTNAME
The query returns the child projects of the parent project, passed in the where clause.
for e.g, here A.PROJECTNAME = 'AAAA_JOB_KJ' is the parent project name being passed.
Also, this query prints the count of rows of the project presents in the table. All this is fine.
I am not able to figure out how to find the count of duplicate data between the parent project and the child project.
For e.g, the table PROJECTNAME has these columns:
PROJECTNAME OBJECTTYPE OBJECTID1 OBJECTVALUE1 OBJECTID2 OBJECTVALUE2 OBJECTID3 OBJECTVALUE3 OBJECTID4 OBJECTVALUE4
My intention is to find the count of the values OBJECTTYPE,OBJECTID1,OBJECTVALUE1, etc which are similar between the two projects, parent project and it's child project.
Parent Project Name Parent Project Count Child Project Child Count Similar Object Count AAAA_JOB_KJ 199 AZ_AUTOFILL_SP1 11 3
The query prepared by me retrieves the output like this:
AAAA_JOB_KJ 199 AZ_AUTOFILL_SP1 11 5
Database in use is Oracle.