RE: Need Good OUTER Join

From: Woody McKay <wmckay_at_hydrogenmedia.com>
Date: Thu, 15 Nov 2001 13:04:41 -0800
Message-ID: <F001.003C6B0F.20011115123027@fatcity.com>

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

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Jacques Kilchoer   [mailto:Jacques.Kilchoer_at_quest.com]Sent: Thursday, November 15,   2001 2:11 PMTo: Multiple recipients of list   ORACLE-LSubject: RE: Need Good OUTER Join   What's wrong with the output you see? <FONT   size=2>Do you not like the results because you are seeing rows where u.user_id   is != 5 ?
>-----Original Message----- <FONT

  size=2>>From: Woody McKay [<A
  href="mailto:wmckay_at_hydrogenmedia.com">mailto:wmckay_at_hydrogenmedia.com]
> >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.   <FONT

  size=2>>======================================================================= 

>  1  select p.page_id,

  substr(p.page_title,1,30), p.page_parent_id, x.security_id <FONT   size=2>>  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 <FONT

  size=2>>SQL> / >   PAGE_ID
  SUBSTR(P.PAGE_TITLE,1,30)      PAGE_PARENT_ID   SECURITY_ID >----------

• -------------- ----------- <FONT size=2>>        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.
  

Received on Thu Nov 15 2001 - 15:04:41 CST