Select records where not in another table. 1

[mrendino]

This is probably easy but I'm just not figuring it out (obviously not a guru). I want to select all of the records in the newinventory table where newinventory.servicetag doesn't have a match in oldinventory.servicetag. Can someone please help?

Thanks, in advance.

 

[r937]

select foo
  from newinventory N
left outer
  join oldinventory O
    on N.servicetag = O.servicetag
 where O.servicetag is null

alternatively,

select foo
  from newinventory N
 where not exists
       ( select servicetag 
           from oldinventory
          where servicetag = N.servicetag )

rudy

 

[rac2]

I think O.servicetag is never null. So this query never returns any rows.

select foo
  from newinventory N
left outer
  join oldinventory O
    on N.servicetag = O.servicetag
 where O.servicetag is null

But I think this will return rows where the new inventory is not in the old inventory.

select foo
  from newinventory N
left outer
  join (
         SELECT O.servicetag
         FROM newinventory N
         left outer join oldinventory O
         on N.servicetag = O.servicetag
        ) O2
    on N.servicetag = O2.servicetag
 where O2.servicetag is null

But I could be wrong.

 

[r937]

select foo
  from newinventory N
left outer
  join oldinventory O
    on N.servicetag = O.servicetag
 where O.servicetag is null

>> "I think O.servicetag is never null.
>> So this query never returns any rows."

on the contrary, that is exactly what you need to look for

it's a left outer join, which means that the join produces all rows of N plus any matching rows of O -- but if, for a given N, there is no matching row of O, then the O columns in that row will be set to null, and when you test for this, then you get only unmatched rows

i think you're trying to do the same with the double left outer join, but it's more complex than necessary

rudy

 

[mrendino]

Thanks so much for your help! It worked like a dream!

 

[rac2]

Hello Rudy,

Right you are. What was I thinking about?

Richard