Member Avatar for 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.

  • 3 Contributors
  • 6 Replies
  • 229 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by atia.akram

Dani AI

Generated

Short summary and recommended approach: the goal is to count distinct "object signatures" (the full set of object columns) that appear in both a given parent project and each candidate child project. The original join returns every matching row and can over-count when the same signature appears multiple times in a project. As discovered, you want the number of distinct signatures shared between two projects. was right to point at DISTINCT, and 's procedural idea works but will be slower at scale — prefer set-based SQL.

A compact, readable solution uses INTERSECT to get the distinct common signatures and a correlated subquery to count them per child:

WITH children AS (
  SELECT DISTINCT projectname AS child
  FROM psprojectitem
  WHERE projectname <> :PARENT
    AND projectname IN (SELECT projectname FROM psprojectdefn WHERE lastupdoprid <> 'pplsoft')
)
SELECT
  :PARENT AS parent_project,
  (SELECT COUNT(*) FROM psprojectitem WHERE projectname = :PARENT) AS parent_count,
  c.child AS child_project,
  (SELECT COUNT(*) FROM psprojectitem WHERE projectname = c.child) AS child_count,
  (SELECT COUNT(*) FROM (
     SELECT objecttype, objectid1, objectvalue1, objectid2, objectvalue2,
            objectid3, objectvalue3, objectid4, objectvalue4
     FROM psprojectitem WHERE projectname = :PARENT
     INTERSECT
     SELECT objecttype, objectid1, objectvalue1, objectid2, objectvalue2,
            objectid3, objectvalue3, objectid4, objectvalue4
     FROM psprojectitem WHERE projectname = c.child
  )) AS similar_object_count
FROM children c
ORDER BY c.child;

If you prefer a single set-based pass (avoid per-child correlated work), pre-aggregate distinct signatures per project and self-join:

WITH parent_sigs AS (
  SELECT DISTINCT objecttype, objectid1, objectvalue1, objectid2, objectvalue2,
                  objectid3, objectvalue3, objectid4, objectvalue4
  FROM psprojectitem
  WHERE projectname = :PARENT
),
child_sigs AS (
  SELECT DISTINCT projectname AS child,
         objecttype, objectid1, objectvalue1, objectid2, objectvalue2,
         objectid3, objectvalue3, objectid4, objectvalue4
  FROM psprojectitem
  WHERE projectname IN (SELECT projectname FROM psprojectdefn WHERE lastupdoprid <> 'pplsoft')
)
SELECT c.child,
       (SELECT COUNT(*) FROM psprojectitem WHERE projectname = c.child) AS child_count,
       COUNT(*) AS similar_object_count
FROM parent_sigs p
JOIN child_sigs c
  ON p.objecttype = c.objecttype
 AND NVL(TO_CHAR(p.objectid1),'<NULL>') = NVL(TO_CHAR(c.objectid1),'<NULL>')
 AND NVL(p.objectvalue1,'<NULL>') = NVL(c.objectvalue1,'<NULL>')
 -- repeat NVL/TO_CHAR comparisons for each id/value pair
GROUP BY c.child
ORDER BY c.child;

Notes and troubleshooting tips:

  • INTERSECT returns distinct rows so duplicates inside one project are not double-counted.
  • Watch nulls: use NVL/TO_CHAR as shown so comparisons behave predictably.
  • Performance: both INTERSECT and DISTINCT require sorting; for large data precompute a signature (hash or function column) or add appropriate indexes. If there are many child projects, the pre-aggregate + join pattern scales better than issuing an INTERSECT per child.

Recommended Answers

All 6 Replies

Member Avatar for Member #949455

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.

Member Avatar for 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

Member Avatar for Member #949455

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.

Member Avatar for 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.

Edited by peterparker
Member Avatar for Member #949455

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

Member Avatar for 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++.

Edited by atia.akram
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.