This assumes that there will always be an entry in tbl_access_rpt for every
entry in tbl_access. If not, you can do an outer join in the 'inline view'
also.
1 select rpt.rep_id
2 from rpt,(select acc_rpt.rpt_id valid_id
3 from acc,acc_rpt
4 where acc.acc_id = acc_rpt.acc_id
5 and acc.user_id = 'abc') user_rpt
6 where
7 rpt.rep_id = user_rpt.valid_id(+)
8* and user_rpt.valid_id is null
SQL> /
REP_ID
3
SQL> desc rpt
Name Null? Type
----------------------------------------- -------- ------------------------
----
REP_ID VARCHAR2(10)
SQL> desc acc
Name Null? Type
----------------------------------------- -------- ------------------------
----
ACC_ID VARCHAR2(10)
USER_ID VARCHAR2(10)
SQL> desc acc_rpt
Name Null? Type
----------------------------------------- -------- ------------------------
----
ACC_ID VARCHAR2(10)
RPT_ID VARCHAR2(10)
=================================================================
Mike
Jason Mowat <jmowat_at_digitalpraxis.com> wrote in message
news:LI5y6.43$kg.1557_at_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 Tue Apr 03 2001 - 21:48:49 CDT
