Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: SQL Problem returning data twice

    I have two tables EMPLOYEE and WORKING, I want to do a select on the table EMPLOYEE that returns all EMPLOYEES not working.

    I use the following SQLPLUS Select statment.

    select emp.name
    from EMPLOYEE emp,WORKING wk
    where wk.employeeID!= emp.employeeID;

    Lets say TOM SMITH is working, but Susan Thomas and Steve Jones is not.

    what this command returns is

    Susan Thomas
    Steve Jones
    Tom Smith
    Susan Thomas
    Steve Jones


    If I run the select satement
    select emp.name
    from EMPLOYEE emp,WORKING wk
    where wk.employeeID = emp.employeeID;

    It only returns

    Tom Smith

    There is a problem with the !=.

    Thanks for any help

    Tony

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select ta.*
    from tableA ta
    LEFT OUTER JOIN
    tableB tb ON
    ta.id = tb.id
    where tb.id IS NULL;

    Alternatively,

    Select ta.*
    from tableA
    where id NOT IN
    (select id
    from tableB);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    10
    Thanks they both worked.

    Cheers Tony

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •