RE: Need Good OUTER Join

Hello all,

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.

Thanks for any help you can give.

Sincerely,

Woody

  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.

[Woody Mckay] 

There are three tables involved:

USERS

• user_id PK

USERS_X_PAGES

• user_id - fk to users
• page_id - fk to pages
• security_id

PAGES

• page_id pk
• page_title