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
