Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    32

    Unanswered: select records not exists in another table

    Hi
    I have two tables
    table1
    column1, column2
    1,1
    2,2
    3,3

    table2
    field1,field2
    1,1
    4,4

    I am trying to select the records in table2 that are not in table1
    or return
    4,4 from the example

    I tried the outer join
    Code:
    select field1, field2 from table2 right join table1 on table1.column1 != table2.field2
    I got
    4 4
    1 1
    4 4
    1 1
    4 4
    from the output

    I don't get it. Any idea?
    much appreciated

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    table2 right join table1
    means give me all rows from the right (table1) and some of table2 based on the on clause

    You need a left join but your condition is incorrect as it will join id=1 from the one table to id 2 and 3 of the other table causing your unwanted results

    Here are 2 ways of doing a not exists query
    Code:
    select *
    from table2 
    left join table1
      on table2.field2=table1.column1
    where table1.column1 is null -- i.e. does not exist in table1
    Code:
     
    select * from table2
    where not exists
    (select 1
     from table1 
     where table1.column1=table2.field2)

Posting Permissions

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