Re: Finding Unmatched Rows

catherine_devlin_at_purpleturtle.com wrote in <9ahpi0$u9v$1_at_news.netmar.com>:

>
>I can't spot the problem offhand, but I think it would be more
>straightforward to do something like
>
>select tbl_rpt.report_id
>from tbl_rpt
>where tbl_rpt.report_id NOT IN
> ( select tbl_access_rpt.report_id
> from tbl_access_rpt , tbl_access
> where tbl_access_rpt.access_id = tbl_access.access_id
> and tbl_access.user_id = 'jmowat' )
>
>Oh, heck, that might not join your tbl_rpt to tbl_access right... well,
>do you get the idea? Using a NOT IN subquery may be more reliable than
>counting on the outer join to handle its unmatched rows the way you're
>hoping it will (handling them exactly as nulls)...
>
>- Catherine

I agree with the "not in" clause but I might try to approach it from the other direction with a NOT EXISTS:

select tr.report_id from tbl_rpt tr
where not exists
  (select tar.report_id from tbl_access_rpt tar, tbl_access ta    where tar.report_id = tr.report_id and

         tar.access_id = ta.access_id and
         ta.user_id = 'jmowat');

Admittedly I haven't tried this out so there might be a syntax error there somewhere, and you could probably optimize it somewhat by checking the access paths, but it's a start.

Dom Received on Wed Apr 18 2001 - 17:18:23 CDT