Two Tables with Left Outer Join & Where Clause

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • pbassutti@hotmail.com

    #1

    Two Tables with Left Outer Join & Where Clause

    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.Emplo yeeID
    FROM Employees LEFT OUTER JOIN Timecards on Employees.Emplo yeeID =
    Timecards.lmpEm ployeeID
    WHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'

    But it doesn't work. However, when I comment the date condition out
    (lmpTimecardDat e = '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.Emplo yeeID
    FROM Employees
    WHERE Employees.Emplo yeeID not in (select Timecards.Emplo yeeID 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

    #2
    Re: Two Tables with Left Outer Join & Where Clause

    <pbassutti@hotm ail.comwrote in message
    news:1193640256 .388620.257210@ i13g2000prf.goo glegroups.com.. .
    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.Emplo yeeID
    FROM Employees LEFT OUTER JOIN Timecards on Employees.Emplo yeeID =
    Timecards.lmpEm ployeeID
    WHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'
    >
    But it doesn't work. However, when I comment the date condition out
    (lmpTimecardDat e = '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.Emplo yeeID
    FROM Employees
    WHERE Employees.Emplo yeeID not in (select Timecards.Emplo yeeID 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
    >
    Put any outer (non-preserved) table references into the ON clause:

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

    --
    David Portas


    Comment

    • Erland Sommarskog

      #3
      Re: Two Tables with Left Outer Join &amp; Where Clause

      (pbassutti@hotm ail.com) writes:
      SELECT Employess.Emplo yeeID
      FROM Employees LEFT OUTER JOIN Timecards on Employees.Emplo yeeID =
      Timecards.lmpEm ployeeID
      WHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'
      >
      But it doesn't work. However, when I comment the date condition out
      (lmpTimecardDat e = '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 "lmpEmploye eID 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.lmpTimecardDa te = '20071014')

      Simply because this clearly express what this is all about.

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


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/pro...ads/books.mspx
      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinf...ons/books.mspx

      Comment

      • pbassutti@hotmail.com

        #4
        Re: Two Tables with Left Outer Join &amp; Where Clause

        On Oct 30, 9:22 am, Erland Sommarskog <esq...@sommars kog.sewrote:
        (pbassu...@hotm ail.com) writes:
        SELECT Employess.Emplo yeeID
        FROM Employees LEFT OUTER JOIN Timecards on Employees.Emplo yeeID =
        Timecards.lmpEm ployeeID
        WHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'
        >
        But it doesn't work. However, when I comment the date condition out
        (lmpTimecardDat e = '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 "lmpEmploye eID 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.lmpTimecardDa te = '20071014')
        >
        Simply because this clearly express what this is all about.
        >
        And I would also use a date format that is safe from misinterpretati ons.
        >
        --
        Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
        >
        Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
        Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
        Thanks a lot guys...

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

        Regards

        Pablo

        Comment

        Working...