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.
Hence, it is clear that the one row is common amongst AAAA_JOB_KJ and AZ_AUTOFILL_SP1, which both have parent and child relationship. So, the count of similar objects amongst them is 1, though, it is more than 1 for these two table. There are 3 rows which have similar object, for these two project name, hence, the count of similar object must return 3.
I want a query which will count the number of similar objects.