RE: Need Good OUTER Join
Title: 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.
RE: Need Good OUTER Join
Title: 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
RE: Need Good OUTER Join
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 PMTo: Multiple recipients of list ORACLE-LSubject: 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.
