1

I have a Person table and Address Table. Their relationship is Person.AddrId = Address.Id

Person table has First_name, Last_name, and AddrId.

The Address table has Address1, Zipcode, and Id.

I want to get all the list of duplicate records from this columns combination.

I tried below query:

SELECT  A.FIRST_NAME, A.LAST_NAME, B.ADDR, B.ZIPCODE, count(1)
FROM  SCHEMA.PERSON A, SCHEMA.ADDRESS B
WHERE 
A.ADDR_ID = B.ID
group by A.FIRST_NAME, A.LASTT_NAME, B.ADDR, B.ZIPCODE 
having count(1) > 1 

Unfortunately this gives only the duplicate records. I want both original as well as duplicate records.

  • 1
    Which DB2 platform? What is the use of showing identical records - or do you want to see the different IDs as well? – MichaelTiefenbacher Mar 21 '16 at 16:52
  • yes, I wanted to see the different Ids and the created date, so it will be useful to analyze when the duplicate got created. this info is also required to business to identify which is the oldest record and which is the newest one. – Prash Mar 21 '16 at 16:56
  • DB2 LUW? DB2 z/OS? – MichaelTiefenbacher Mar 21 '16 at 16:57
  • DB2 platform is DB2 LUW – Prash Mar 22 '16 at 10:10
2

SQL is closed so the result of a query is a new table that can be futher used. You can therefore join the result of your duplicates query with the original tables. Something like below should work:

select t2.*
from (
    SELECT  A.FIRST_NAME, A.LAST_NAME, B.ADDR, B.ZIPCODE
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
    group by A.FIRST_NAME, A.LASTT_NAME, B.ADDR, B.ZIPCODE 
    having count(1) > 1
) as t1
join ( 
    SELECT  A.*, B.*
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
) as t2
     on (t1.FIRST_NAME, t1.LAST_NAME, t1.ADDR, t1.ZIPCODE)
      = (t2.FIRST_NAME, t2.LAST_NAME, t2.ADDR, t2.ZIPCODE)

Using a CTE will probably be a bit more efficient:

 with t as (
    SELECT  A.*, B.*
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
)
select t1.*
from t1
join (
    SELECT  FIRST_NAME, LAST_NAME, ADDR, ZIPCODE
    FROM  T1
    group by FIRST_NAME, LASTT_NAME, ADDR, ZIPCODE 
    having count(1) > 1
) as t2
    on (t1.FIRST_NAME, t1.LAST_NAME, t1.ADDR, t1.ZIPCODE)
     = (t2.FIRST_NAME, t2.LAST_NAME, t2.ADDR, t2.ZIPCODE)

I replaced the "," joins with explicit ones, I also used * since I did not know the names of the additional columns.

  • Thanks very much, the solution which you have given is working perfectly. – Prash Mar 22 '16 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.