Finding count of similar objects used by two different rows

[peterparker]

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.

Sample output:

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.

Thanks.

[LastMitch]

I am not able to figure out how to find the count of duplicate data between the parent project and the child project.

Try used SELECT DISTINCT. Don't have the database to test it out.

[peterparker]

Try used SELECT DISTINCT. Don't have the database to test it ou

Oh you mean to say, I have to write SELECT DISTINCT COUNT(*) and then some thing like MINUS operator. Thanks

[LastMitch]

Oh you mean to say, I have to write SELECT DISTINCT COUNT(*) and then some thing like MINUS operator. Thanks

Yes, kinda like that. Distinct will isolate the duplicate.

[peterparker]

The data is like this:

The query result is as follows:

SELECT * FROM PSPROJECTITEM WHERE projectname = 'AAAA_JOB_KJ';
One such row in this query is:

AAAA_JOB_KJ 8 1 JOB 2 EMPL_RCD 12 SavePostChange 0
Similarly:

SELECT * FROM PSPROJECTITEM WHERE PROJECTNAME = 'AZ_AUTOFILL_SP1';
One such row is:

AZ_AUTOFILL_SP1 8 1 JOB 2 EMPL_RCD 12 SavePostChange 0

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.

[LastMitch]

I want a query which will count the number of similar objects.

I think I mention that I don't have Oracle nor a database on my computer.

If you need a query then you need to get familiar with the query code. SELECT DISTINCT COUNT() is one simple solution, finding similarity which you already did by using GROUP BY and ORDER BY

You can take a look at this:

http://docs.oracle.com/cd/B12037_01/server.101/b10821/expressionconcepts.htm

[atia.akram]

make use of subquery that compare every object of parent with child objects then on true,do increment the count.
it is similar to loop in c++.