Finding Unmatched Rows

From: Jason Mowat <jmowat_at_digitalpraxis.com>
Date: Mon, 02 Apr 2001 20:57:15 GMT
Message-ID: <LI5y6.43$kg.1557@news2.mts.net>

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 Received on Mon Apr 02 2001 - 15:57:15 CDT