From wmckay@hydrogenmedia.com Thu, 15 Nov 2001 13:04:41 -0800
From: "Woody McKay" <wmckay@hydrogenmedia.com>
Date: Thu, 15 Nov 2001 13:04:41 -0800
Subject: RE: Need Good OUTER Join
Message-ID: <F001.003C6B0F.20011115123027@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain
Title: RE: Need Good OUTER Join



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@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@hydrogenmedia.com">mailto:wmckay@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.