Re: Two Tables with Left Outer Join & Where Clause

On Oct 30, 9:22 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
(pbassu...@xxxxxxxxxxx) writes:
SELECT Employess.EmployeeID
FROM Employees LEFT OUTER JOIN Timecards on Employees.EmployeeID =
Timecards.lmpEmployeeID
WHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'

But it doesn't work. However, when I comment the date condition out
(lmpTimecardDate = '10/24/2007') it works all right but It's not what

In addition to David's post, here is what is happening:

The FROM ... LEFT JOIN operators define a table that includes all rows
in the outer table, Employees in this case. This table includes the columns
from the Timecards table, but for the employees there there is no timecard,
all columns have NULL. Which you apparently have understood, since you
the condition "lmpEmployeeID IS NULL". But then there is a lapse, and you
filter lmpTimecardDate despite it is not likely that there is a row in
Timecards where the date is non-NULL and the employee ID is NULL. (At least
one would hope so!) Moving the date condition to the ON clause addresses
the issue, as it now will be part of the condition that builds the
table that is then filtered by WHERE.

Personally, I would prefer to write this query with NOT EXISTS:

SELECT E.Employee
FROM Employees E
WHERE NOT EXISTS (SELECT *
FROM Timecards T
WHERE E.EmployeeID = T.lmpEmployeeID
AND T.lmpTimecardDate = '20071014')

Simply because this clearly express what this is all about.

And I would also use a date format that is safe from misinterpretations.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks a lot guys...

That worked perfectly... and thanks for the explanation and
suggestions

Regards

Pablo

.

Relevant Pages

  • Re: DCount Code Problem ?
    ... >> The db is a invoice database. ... >> history of payments, count how many employees worked on what job, what ... >> help with is the sub-form Time And Hours on the main form TimeCards, ...
    (microsoft.public.access.forms)
  • Re: Two Tables with Left Outer Join & Where Clause
    ... WHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007' ... in the outer table, Employees in this case. ... from the Timecards table, but for the employees there there is no timecard, ...
    (comp.databases.ms-sqlserver)
  • Re: DCount Code Problem ?
    ... > The db is a invoice database. ... > help with is the sub-form Time And Hours on the main form TimeCards, ... >> Van T. Dinh ... >>> I need to get the number of records that have more than 2 employees on ...
    (microsoft.public.access.forms)
  • Re: DCount Code Problem ?
    ... The db is a invoice database. ... history of payments, count how many employees worked on what job, what date ... help with is the sub-form Time And Hours on the main form TimeCards, ... The RecordSource of the Main Form. ...
    (microsoft.public.access.forms)
  • Re: Two Tables with Left Outer Join & Where Clause
    ... I need to get a list of employees that do not have timecards on an ... WHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007' ... I'd like to be able to use the Left Outer Join option.... ...
    (comp.databases.ms-sqlserver)