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

On Wed, 20 Nov 2002 13:29:06 +0000, 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.
>
> Thanks in advance for any help!
> --
> Matt

Assuming A is the parent table and B is the table where you would like to have a f.k. to A

        select b from B where not exists (select 1 from A where a = B.b)

I.e. select the rows from B that does not have a parent row in A

HTH
/Lennart Received on Wed Nov 20 2002 - 21:41:27 CET