SQLTeam.com | Weblogs | Forums

Compare tables and avoid duplicates

Hi All.
How to modify this select

SELECT T1.*
FROM dbo.Table1 T1
WHERE NOT EXISTS (
SELECT 1
FROM dbo.Table2 T2
WHERE T2.Employee_ID = T1.Employee_ID AND T2.Department_ID = 9
)

to retrieve columns from both table and avoid duplicates?

Thanks.

what are the keys on T1 and T2? Also, "to retrieve columns from both table and avoid duplicates" doesn't work with not exists

Hi mike01. Thanks for reply.
T1 primary key Employee_ID. T2 primary key say ID, foreign key Employee_ID.

Thanks.

What do you consider duplicates? If you join the tables on EmployeeID - and Table2 has multiple departments identified for a single employee - which one do you want?

It is going to depend on what you have determined are duplicate entries. Once you have that - you can use a CTE for the main query, adding a row_number() function to identify the 'duplicate' rows and then select from the CTE where the row number = 1.

If it is more complex than that - then you might need a CROSS APPLY with a SELECT TOP 1...ORDER BY to get the correct non-duplicate.