Re: Finding Unmatched Rows

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

In article <LI5y6.43$kg.1557_at_news2.mts.net>, Jason Mowat <jmowat_at_digitalpraxis.com> writes:
>Greetings,
>
>I am having some problems finding unmatched rows using an outer join, and I
>am hoping someone can help me out. I have the following tables:
>
>tbl_rpt
>=====
>report_id (PK)
>
>tbl_access
>=======
>access_id (PK)
>user_id
>
>tbl_access_rpt
>==========
>access_id (PK) (FK)
>report_id (FK)
>
>Essentially, I have a group of reports. I have a user (jmowat) who
>currently has access to some of the reports on the system. I want to find
>out which reports HE DOES NOT have access to. I wrote the following SQL,
>which returns NO ROWS:
>
>select tbl_rpt.report_id, tbl_access_rpt.report_id
>from tbl_rpt, tbl_access, tbl_access_rpt
>where
>tbl_access.access_id = tbl_access_rpt.access_id
>and tbl_access.user_id = 'jmowat'
>and tbl_access_rpt.report_id (+) = tbl_rpt.report_id
>and (( tbl_access_rpt.report_id is null ))
>order by tbl_rpt.report_id
>
>What am I doing wrong here? My test data is as follows (example):
>
>tbl_rpt
>=====
>1
>2
>3
>4
>
>tbl_access
>=======
>1,jmowat
>2,jmowat
>3,jmowat
>
>tbl_access_rpt
>==========
>1,1
>2,2
>3,4
>
>Note, that if I do an outer join with tbl_access_rpt on report_id (my data
>deficient table) to tbl_rpt.report_id, I will get a NULL value for report_id
>'3'. I can't seem to simulate this on Oracle.
>
>Cheers,
>Jason
>
>

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net