I want to find out which Employees have a contract and whether they've
worked and if so, the last work day

the tables concerned are :
tblEmployee : table holding all records of Employees
tblContracts : table holding all contract of Employees (not every employee
has a contract)
tblWorkDays : table holding all records of dates worked by Employees (not
every employee has worked)

This is the query :

"SELECT DISTINCT w.ID_emp, w.name,c.ID," _
& " lastWorkDayStr = CASE WHEN EXISTS(SELECT TOP 1 date FROM tblWorkDays
WHERE empID=w.wrnID  ORDER BY date DESC)  THEN date ELSE 0 END " _
& " FROM tblContracts c,tblEmployee w,tblWorkDays g WHERE  c.wrnID=w.ID_emp
"


For a reason that I don't know the results are too many. While I expect only
one record for each employee who is contracted and holding the value of the
last worked day it results that there are multiple records for the same
employee which differ only for the value of the last day worked. Important
to say, I can't make any restriction with respect to tblWorkDays in the last
part of the query, since it is not necessary that the employee has working
days. In case I omit the tblWorkDays within the last part of the query, SQL
Server fires an error that it couldn't find the field 'date'. Any idea how I
can solve this problem?

Oscar

Using ranking functions in a subquery to feed a CLR function values in a specified order.
Challenging Query - Please Help
Convert *= / =* to outer joins (ANSI compliant)
SQL Include Empty Months
create a sp from query output