Re: How to get only the non-matching entries from an outer join
From: Heinz Huber <hhuber_at_racon-linz.at>
Date: Wed, 27 Nov 2002 10:57:38 +0100
Message-ID: <3de49713$0$37624$91cee783_at_newsreader01.highway.telekom.at>
>>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.
Date: Wed, 27 Nov 2002 10:57:38 +0100
Message-ID: <3de49713$0$37624$91cee783_at_newsreader01.highway.telekom.at>
Mike Sherrill wrote:
> 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);
AFAIK, this will give you all rows from T2 without any data from T1. The where clause is applied BEFORE the outer join. Therefore all rows from T1 are filtered out since the PK cannot be NULL.
Regards,
Heinz
Received on Wed Nov 27 2002 - 10:57:38 CET