based on your last query,
the DUP CTE is the query that identify the duplicates. So if you have 3 fields, it will be like FirstName, LastName, Num
; WITH DUP
AS (
SELECT FirstName,
LastName,
Num,
COUNT(*) AS NumberDuplicates
FROM table1
WHERE Num IS NOT NULL
GROUP BY FirstName,
LastName,
Num
HAVING (COUNT(*) > 1)
)
the second part of the query DUP joining to table1t is for you to show the required columns.
FROM DUP d
INNER JOIN table1t t
ON d.FirstName = t.FirstName
AND d.LastName = t.LastName
AND d.Num = t.Num -- added this line
Yes. you are correct. So by right, you should have another JOIN condition on Num