Re: orphaned records

Dave Salvador <dsalvador_at_mail.walmar.com> wrote in message news:B518C079.160%dsalvador_at_mail.walmar.com...
> I have two tables, with the 2nd table having a foreign key relationship to
> the first. Could someone suggest a query that would allow me to find any
> orphaned records.
>
> Thanks

Two ways:

1. Use an outer join, for example,

select e.*
  from emp e, dept d
  where e.deptno=d.deptno(+)
    and d.deptno is null;

2. Use the exceptions table:

alter table emp enable constraint fk_deptno exceptions into exceptions; select * from emp
  where rowid in (select row_id from exceptions where owner='SCOTT'     and table_name='EMP' and constraint='FK_DEPTNO'); Received on Thu Apr 13 2000 - 00:00:00 CDT