Re: Finding Unmatched Rows

An outer join will not give you the answer you seek. Look at MINUS.

For a simple example that finds everything in one table not in another:

SELECT field1
FROM table1
MINUS
SELECT field1
FROM table2

Daniel A. Morgan

>
> 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 - 16:34:56 CDT