locked
Get rows that are not equal? RRS feed

  • Question

  • I have two tables. table1 and table2.
    table1 has empID, workOutHour. table2 has empID and eligibleEmployee.
    table2 has list of eligible employeeID who can workout and table1 has only those employeeID who workouts.
    so now i need to return those employeeID from table1, which does not exists in table2?

    Any suggestions
    creativity..
    Monday, April 27, 2009 6:09 PM

Answers

  • Hello,

    Try this

    SELECT Tab1.EmployeeID, *
    FROM Table1 AS Tab1
    LEFT JOIN Table2 AS Tab2
    ON Tab1. EMployeeID = Tab2.EMployeeID
    WHERE Tab2.EmployeeID IS NULL

    Hope helpful...


    Pavan http://www.ggktech.com
    • Marked as answer by Kent Waldrop _ Wednesday, April 29, 2009 11:27 AM
    Monday, April 27, 2009 6:11 PM
  • Another alternative is to use the NOT EXISTS clause; something like:

    select *
    from table1 a
    where not exists
    ( select 0 from table2 b
      where a.employeeId = b.employeeId
    )
    Kent Waldrop Ap09
    • Marked as answer by Kent Waldrop _ Wednesday, April 29, 2009 11:28 AM
    Monday, April 27, 2009 6:13 PM
  • here is the solution:
    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a92cb871-ea8a-4ea0-850a-d064099067c2/
    creativity..
    Monday, April 27, 2009 6:14 PM

All replies

  • Hello,

    Try this

    SELECT Tab1.EmployeeID, *
    FROM Table1 AS Tab1
    LEFT JOIN Table2 AS Tab2
    ON Tab1. EMployeeID = Tab2.EMployeeID
    WHERE Tab2.EmployeeID IS NULL

    Hope helpful...


    Pavan http://www.ggktech.com
    • Marked as answer by Kent Waldrop _ Wednesday, April 29, 2009 11:27 AM
    Monday, April 27, 2009 6:11 PM
  • Another alternative is to use the NOT EXISTS clause; something like:

    select *
    from table1 a
    where not exists
    ( select 0 from table2 b
      where a.employeeId = b.employeeId
    )
    Kent Waldrop Ap09
    • Marked as answer by Kent Waldrop _ Wednesday, April 29, 2009 11:28 AM
    Monday, April 27, 2009 6:13 PM
  • here is the solution:
    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a92cb871-ea8a-4ea0-850a-d064099067c2/
    creativity..
    Monday, April 27, 2009 6:14 PM