SQLTeam.com | Weblogs | Forums

How to retrieve data from one table that not present in another

Hi All.
I have two related tables where Employee_ID is foreign of Table2 and primary key of Table1.
How to create select where Employee_ID from Table1 that not presented in this select

select Employee_ID
from Table2
where Department_ID = 9

Thanks

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
    )

Thanks a lot

You're welcome.

Hi ScottPletcher.
Does it possible to modify that 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.