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

On Wed, 20 Nov 2002 13:29:06 -0600, "Matt Hughes" <mhughes_at_nospam.eeek> 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.

Something like this?

 SELECT T2.FK, T1.PK
 FROM T2
 LEFT JOIN T1 ON T2.FK = T1.PK
 WHERE (T1.PK Is Null);

-- 
Mike Sherrill
Information Management Systems