Re: How to get only the non-matching entries from an outer join

Matt Hughes wrote:

> This comes up all the time. I'm trying to add a foreign key that wasn't
> previously modelled, but there are records that don't have their foreign
> keys in place yet for whatever reason. Usually a parent was dropped and
> without the FK modelled it couldn't cascade leaving the orphaned records.
>
> I end up finding these by doing an outer join to a temp table, and then
> iterating through the temp table looking for NULLs and reporting on them, or
> clearing out the "bad" records.
>
> There ought to be a better way to do this, but for the life of me, I can't
> think of it. I'm worried this is one of those questions with an obvious
> answer, but it just hasn't popped into my mind.

Correlated subquery?

select *
from child_table c
where not exists
  (select 1
   from parent_table p
   where c.fkey = p.pkey
  )

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Wed Nov 20 2002 - 23:46:23 CET