Re: How to get only the non-matching entries from an outer join
Date: Fri, 22 Nov 2002 08:18:32 -0500
Message-ID: <n9crtuksccr40ect28iofs64icu5mhpqdi_at_4ax.com>
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 SystemsReceived on Fri Nov 22 2002 - 14:18:32 CET