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.
Best Answer
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:
Using a CTE will probably be a bit more efficient:
I replaced the "," joins with explicit ones, I also used * since I did not know the names of the additional columns.