help with nested Query

Hi
I have 2 tables. The first has employee information and the second has
payroll information. I need to find out people who are not in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter it and find
out for each payroll.
I don't think i have explained it very well so here is the data set.
hope someone can help me with this.
Thanks in advance
prit

Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333

TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333

Required RESULT required(Missing employees from payroll)
IDNumSSN
1333333333
2111111111I think this could be what you're looking for:

SELECT I.idnum, E.ssn
FROM
(SELECT DISTINCT idnum
FROM PayrollDetail) AS I
CROSS JOIN Employees AS E
LEFT JOIN PayrollDetail AS D
ON I.idnum = D.idnum
AND E.ssn = D.ssn
WHERE D.idnum IS NULL

If you have another table for the entity represented by Idnum then use that
table in place of the derived table "I".

--
David Portas
SQL Server MVP
--