RE: Need Good OUTER Join

[Woody McKay]

Title: RE: Need Good OUTER Join

The problem is there are duplicate data for the page_id and page_title.

-----Original Message-----
From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 15, 2001 2:11 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Need Good OUTER Join

What's wrong with the output you see?
Do you not like the results because you are seeing rows where u.user_id is != 5 ?

>-----Original Message-----
>From: Woody McKay [mailto:[EMAIL PROTECTED]]
>
>I'm having trouble figuring out the proper Outer join (duplicate titles) for the following:
>I need a list of all the page_id (id's) and page_title (titles) from PAGES along with the security_id from USERS_X_PAGES where the user exists.  USERS_X_PAGES may or may not have any records.  Below is my current query and table layout.

>=======================================================================
>  1  select p.page_id, substr(p.page_title,1,30), p.page_parent_id, x.security_id
>  2  from pages p, users_x_pages x, users u
>  3  where p.page_id = x.page_id(+)
>  4  and x.user_id = u.user_id(+)
>  5  and u.user_id(+) = 5
>  6* order by page_title
>SQL> /
>   PAGE_ID SUBSTR(P.PAGE_TITLE,1,30)      PAGE_PARENT_ID SECURITY_ID
>---------- ------------------------------ -------------- -----------
>        48 Building Construction & Manage SQL                      2
>        48 Building Construction & Manage SQL                      1
>        48 Building Construction & Manage SQL                      2
>        48 Building Construction & Manage SQL                      2
>        53 Communications                 SQL                      1
>        47 DMS                            SQL                      2
>        47 DMS                            SQL                      2
>        63 DMS Hot Topics                 SQL                      2
>        58 Division of Retirement         SQL                      2
>        62 Governor's Area                SQL            SQL
>        56 HR Management                  SQL                      2
>        49 Inspector General              SQL            SQL
>        51 Legislative Affairs            SQL            SQL
>        50 Personnel Management           SQL                      2
>        52 Planning & Budget              SQL            SQL
>        61 Secretary Bio                  SQL            SQL
>        55 State Fleet Management         SQL            SQL
>        60 State Group Insurance          SQL            SQL
>        57 State Purchasing               SQL            SQL
>        59 Supplier Diversity             SQL            SQL
>20 rows selected.