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
PlanID SSN
1001 111111111
1001 222222222
1001 333333333

TblPayrolldetail
IDNum PlanID SSN
1 1001 111111111
1 1001 222222222
2 1001 222222222
2 1001 333333333

Required RESULT required(Missing employees from payroll)
IDNum SSN
1 333333333

I 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
--