Find missing entries 1

[jmadd]

I have an employees table with employeeid
I have a timesheet table with employeeid and weekid

I would like to query weekid and find employeeid who have no entries for that week. Can't seem to get anything to work. Anybody please. What's killing me is the comparison of employees.employeeid and timesheets.employeeid.

 

[rac2]

SELECT Employees.employeeid,
            Timesheets.weekid
FROM Timesheets
LEFT JOIN Employees ON Employees.employeeid = Timesheets.employeeid
WHERE Employees.employeeid IS NULL

 

[jmadd]

Something is not right. I'll play around with what you have to see if I can figure it out. It's missing the weekid I would like to search for. Thanks.

 

[r937]

select Employees.employeeid
  from Employees
left outer
  join Timesheets 
    on Employees.employeeid 
     = Timesheets.employeeid
   and Timesheets.weekid = 21
 where Timesheets.employeeid is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jmadd]

that did it, thanks.

 

[dnoeth]

Hey, this is not supposed to be a mysql forum ;-)

In Standard SQL it's usually done with a NOT EXISTS:

select employeeid
from Employees
where not exists
(select *
from timesheets
where Employees.employeeid
= Timesheets.employeeid
and Timesheets.weekid = 21
)

Dieter

 

[jmadd]

You have no idea what sanity your post has given back to me. For almost 2 days I tried every variation of not exists I could think of. Syntax was right, what's the problem? I inherited a mysql database from an RIT co-op who created an employee time sheet program for us. Wasn't aware (although it would have eventually hit me) there was difference. Thanks.

 

[r937]

i have no idea why you thought it was mysql

my solution is ansi sql too!!

;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)