|
|
Posted by pbassutti on 10/30/07 00:44
On Oct 30, 9:22 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (pbassu...@hotmail.com) 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...@sommarskog.se
>
> 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
Navigation:
[Reply to this message]
|