Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Other SQL Server Topics / October 2007

Tip: Looking for answers? Try searching our database.

Two Tables with Left Outer Join & Where Clause

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
pbassutti@hotmail.com - 29 Oct 2007 07:44 GMT
Hello,

I'm trying to link two tables... one for Employees and the other for
Timecards

I need to get a list of employees that do not have timecards on an
SPECIFIC DATE

I tried the follonwing

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

Another interesting point... if I use the following query... it works
all right

SELECT Employess.EmployeeID
FROM Employees
WHERE Employees.EmployeeID not in (select Timecards.EmployeeID from
Timecards
                                  where TimecardDate = '10/24/2007')

I'd like to be able to use the Left Outer Join option.... Am I doing
something wrong?... or is it that if It doesn't like the condition I'm
usgin in the WHERE clause (TimecardDate = '10/24/2007')

Thanks for your help

Pablo
David Portas - 29 Oct 2007 08:16 GMT
> Hello,
>
[quoted text clipped - 31 lines]
>
> Pablo

Put any outer (non-preserved) table references into the ON clause:

SELECT Employess.EmployeeID
FROM Employees
LEFT OUTER JOIN Timecards
ON Employees.EmployeeID = Timecards.lmpEmployeeID
 AND lmpTimecardDate = '20071024'
WHERE lmpEmployeeID is NULL ;

Signature

David Portas

Erland Sommarskog - 29 Oct 2007 23:22 GMT
> SELECT Employess.EmployeeID
> FROM Employees LEFT OUTER JOIN Timecards on Employees.EmployeeID =
[quoted text clipped - 3 lines]
> 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.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

pbassutti@hotmail.com - 30 Oct 2007 01:44 GMT
>  (pbassu...@hotmail.com) writes:
> > SELECT Employess.EmployeeID
[quoted text clipped - 36 lines]
> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.