Reputation: 695
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
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
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
Reputation: 1763
SELECT a.PROJECTNAME,
count(a.PROJECTNAME) as count
FROM PSPROJECTITEM a
GROUP BY a.PROJECTNAME HAVING count(a.PROJECTNAME) >1
Upvotes: -1
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