CODEWITHSUNDEEP
sqloracle-database
vamosrafa
vamosrafa

Reputation: 695

How to find duplicate data

I am writing a SQL query, where my goal is to find the duplicate values based on the column names:

SELECT a.PROJECTNAME 
 , a.OBJECTTYPE 
 , a.OBJECTID1 
 , a.OBJECTVALUE1 
 , a.OBJECTID2 
 , a.OBJECTVALUE2 
 , a.OBJECTID3 
 , a.OBJECTVALUE3 
 , a.OBJECTID4 
 , a.OBJECTVALUE4 
  FROM PSPROJECTITEM a 
 WHERE a.projectname  = 'AZ_HCM_745' 
 AND 1 < ( 
 SELECT COUNT(*) 
  FROM PSPROJECTITEM c 
 WHERE a.objecttype = c.objecttype 
   AND a.objectid1 =c.objectid1 
   AND a.objectvalue1 = c.objectvalue1 
   AND a.objectid2 = c.objectid2 
   AND a.objectvalue2 = c.objectvalue2 
   AND a.objectid3 = c.objectid3 
   AND a.objectvalue3 = c.objectvalue3 
   AND a.objectid4 = c.objectid4 
   AND a.objectvalue4 = c.objectvalue4) 
  ORDER BY a.projectname

My intention is to find those values, which are duplicate of a.projectname, I mean, the output should display the duplicate value of AZ_HCM_745, means, it should have same fields like objecttype, objectid and even the count of the object.

The output I am looking for this like this:

PROJECTNAME OBJECTTYPE  OBJECTID1   OBJECTVALUE1    OBJECTID2 OBJECTVALUE2  OBJECTID3   OBJECTVALUE3    OBJECTID4   OBJECTVALUE4

These are the fields name which I am selecting from the query.

Now i am passing a.projectname = 'AZ_HCM_745'.

My goal is to find the data which are duplicate of AZ_HCM_745 and the projectname which have these values, for eg:

AZ_HCM_745  0   1   AUDIT_AZ_ADP11P 0       0       0

is the original value.

The duplicated value is:

AZ_HCM_745_BKP  0   1   AUDIT_AZ_ADP11P 0       0       0

Please note that projectname can vary,_bkp or _a, my goal is to find the projectnames which have duplicate values of the objecttype, objectid1, and i want to select these value.

Also, the query must fetch only the duplicate values of the project name which is being passed in the parameter, not the projectname, means the original value must not be displayed, only the duplicates must be displayed

Database in use is Oracle.

Upvotes: 0

Views: 287

Answers (4)

Low Chee Mun
Low Chee Mun

Reputation: 610

Correct me if I am wrong, your objective is to use project name to find the duplicated value in PSPROJECTITEM.

SELECT t2.objecttype
,t2.objectid1
,t2.objectvalue1
,t2.objectid2
,t2.objectvalue2
,t2.objectid3
,t2.objectvalue3
,t2.objectid4
,t2.objectvalue4
FROM PSPROJECTITEM t2, PROJECTNAME t1
WHERE t2.objecttype = t1.objecttype
AND t2.objectid1 = t1.objectid1
AND t2.objectvalue1 = t1.objectvalue1
AND t2.objectid2 = t1.objectid2
AND t2.objectvalue2 = t1.objectvalue2
AND t2.objectid3 =t1.objectid3
AND t2.objectvalue3 = t1.objectvalue3
AND t2.objectid4 = t1.objectid4
AND t2.objectvalue4 = t1.objectvalue4
AND t1.projectname   = 'AZ_HCM_745'

if there are unique ID in your database, please use it

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

The main problem with your query is that it is "inside-out". That is, you are only selecting the project of interest in the outer query. The inner is selecting all the similar ones, but they are not being output because they are in a where clause.

Try this variation:

SELECT a.PROJECTNAME 
 , a.OBJECTTYPE 
 , a.OBJECTID1 
 , a.OBJECTVALUE1 
 , a.OBJECTID2 
 , a.OBJECTVALUE2 
 , a.OBJECTID3 
 , a.OBJECTVALUE3 
 , a.OBJECTID4 
 , a.OBJECTVALUE4 
  FROM PSPROJECTITEM a 
 WHERE a.projectname <> 'AZ_HCM_745' and
       exists ( 
 SELECT *
  FROM PSPROJECTITEM c 
 WHERE c.projectname  = 'AZ_HCM_745' and
       a.objecttype = c.objecttype 
   AND a.objectid1 =c.objectid1 
   AND a.objectvalue1 = c.objectvalue1 
   AND a.objectid2 = c.objectid2 
   AND a.objectvalue2 = c.objectvalue2 
   AND a.objectid3 = c.objectid3 
   AND a.objectvalue3 = c.objectvalue3 
   AND a.objectid4 = c.objectid4 
   AND a.objectvalue4 = c.objectvalue4) 
  ORDER BY a.projectname

A secondary consideration is that NULL values will not match (you'll need to use something like coalesce() if this is an issue).

Upvotes: 4

Goutam Pal
Goutam Pal

Reputation: 1763

SELECT a.PROJECTNAME,
count(a.PROJECTNAME) as count
FROM PSPROJECTITEM a 
GROUP BY a.PROJECTNAME HAVING count(a.PROJECTNAME) >1

Upvotes: -1

Ahmed
Ahmed

Reputation: 450

You can try this :


  SELECT A.PROJECTNAME,A.objectid, A.objecttype ,COUNT(*) DUBLICATE FROM PSPROJECTITEM A GROUP BY A.PROJECTNAME,A.objectid, A.objecttype HAVING COUNT(*)>1

Upvotes: -1

Related Questions