Home » SQL & PL/SQL » SQL & PL/SQL » Master Detail with Connect by (10.2.0.3)
Master Detail with Connect by [message #317354] |
Wed, 30 April 2008 16:47  |
rlockard
Messages: 19 Registered: April 2008 Location: Baltimore, MD
|
Junior Member |
|
|
I have two tables Departments and Projects. I am creating a form with a tree.
Departments
id number primary key,
name varchar2(65) not null,
parent_id number references departments.id,
...
Projects
id number primary key,
name varchar2(65) not null,
dept_id number references departments.id
...
Now, I can use the connect by to get the department hierarchy easily. But for each department I would like to have the project assigned to the department at level+1.
Any idea on how to do this?
-Rob
|
|
|
|
Re: Master Detail with Connect by [message #317370 is a reply to message #317354] |
Wed, 30 April 2008 19:19   |
rlockard
Messages: 19 Registered: April 2008 Location: Baltimore, MD
|
Junior Member |
|
|
Ummmm, sarcasism. Not a good way to earn my respect.
sql with connect by on one table is easy. This is for two table master detail.
If you don't have something.intelegent to contribute then don't post.
Rob
|
|
|
Re: Master Detail with Connect by [message #317371 is a reply to message #317354] |
Wed, 30 April 2008 19:58   |
rlockard
Messages: 19 Registered: April 2008 Location: Baltimore, MD
|
Junior Member |
|
|
Here is a little more detail just to avoid confusion.
This is the type of output I am looking for.
DEPARTMENT1
|__DEPARTMENT2
| |__PROJECT1
| |__PROJECT2
|__DEPARTMENT3
|__DEPARTMENT4
. |__PROJECT3
To get the departments I would use this query. (actual query)
select 1, level, name, null, to_char(id)
from departments
start with parent_id is null
connect by prior id = parent_id;
-Rob
[Updated on: Wed, 30 April 2008 20:00] Report message to a moderator
|
|
|
|
Re: Master Detail with Connect by [message #317384 is a reply to message #317372] |
Wed, 30 April 2008 22:48   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Write a simple left-join-query to join the Department and Project - this will preserve the branch-Departements that have no Projects. Then add a CONNECT BY clause to that SQL.
Ross Leishman
|
|
|
|
Re: Master Detail with Connect by [message #317451 is a reply to message #317384] |
Thu, 01 May 2008 07:36   |
rlockard
Messages: 19 Registered: April 2008 Location: Baltimore, MD
|
Junior Member |
|
|
rleishman wrote on Wed, 30 April 2008 23:48 | Write a simple left-join-query to join the Department and Project - this will preserve the branch-Departements that have no Projects. Then add a CONNECT BY clause to that SQL.
Ross Leishman
|
I tried that when I first started down this rabbit hole. Good try, but that does not solve the problem.
I found two metalink notes on this. One is a pl/sql solution. Note:104791.1 The other is using nested table Note:143731.1 Now because this is a production system, changing to nested table is not an option.
This reminded me why I stopped posting on list ten years ago. Anyone with a internet connection can declare them selfs and expert. But the noise to signal ratio around here sucks. I'm out of here, enjoy your circle jerk.
-Rob
|
|
|
Re: Master Detail with Connect by [message #317456 is a reply to message #317451] |
Thu, 01 May 2008 08:00   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Maybe if you posted some code you could explain why this is not what you want.
SQL> create table departments
2 (id number primary key
3 , name varchar2(65) not null,
4 parent_id number references departments (id));
Table created.
SQL> create table projects
2 (id number primary key,
3 name varchar2(65) not null,
4 dpt_id number references departments (id));
Table created.
SQL> insert into departments ( id, name, parent_id) values (1, 'DEPARTMENT1', null);
1 row created.
SQL> insert into departments ( id, name, parent_id) values (2, 'DEPARTMENT2', 1);
1 row created.
SQL> insert into departments ( id, name, parent_id) values (3, 'DEPARTMENT3', 1);
1 row created.
SQL> insert into departments ( id, name, parent_id) values (4, 'DEPARTMENT4', 1);
1 row created.
SQL> insert into projects (id, name, dpt_id) values (1, 'PROJECT1', 2);
1 row created.
SQL> insert into projects (id, name, dpt_id) values (2, 'PROJECT2', 2);
1 row created.
SQL> insert into projects (id, name, dpt_id) values (3, 'PROJECT3', 4);
1 row created.
SQL> commit;
Commit complete.
SQL> select lpad(' ', (level - 1) * 4, ' ')||dpt.name||' '||pjt.name
2 from departments dpt
3 , projects pjt
4 where pjt.dpt_id(+) = dpt.id
5 connect by dpt.parent_id = prior dpt.id
6 start with dpt.parent_id is null
7 order siblings by dpt.name
8 , pjt.name;
LPAD('',(LEVEL-1)*4,'')||DPT.NAME||''||PJT.NAME
------------------------------------------------------------------------------------
DEPARTMENT1
DEPARTMENT2 PROJECT1
DEPARTMENT2 PROJECT2
DEPARTMENT3
DEPARTMENT4 PROJECT3
|
|
|
Re: Master Detail with Connect by [message #317457 is a reply to message #317456] |
Thu, 01 May 2008 08:21   |
rlockard
Messages: 19 Registered: April 2008 Location: Baltimore, MD
|
Junior Member |
|
|
Oh, yes. That is what I got when I started this. Thank you for the effort. There is one difference that I need. Project should be another row, not on the same row.
So, next time I'll post the code to show what is happening. Now if we can get project at level+1 on department.
ie.
. DEPARTMENT1
. |
. DEPARTMENT2
. |__PROJECT1
. DEPARTMENT2
. |__PROJECT2
. DEPARTMENT3
. |
. DEPARTMENT4
. |__PROJECT3
This is going into a Forms tree where the user can select a department or project. If the user selects a department, then they get one tab, if they select a project they get another tab.
Thanks again,
-Rob
Frank wrote on Thu, 01 May 2008 09:00 | Maybe if you posted some code you could explain why this is not what you want.
SQL> select lpad(' ', (level - 1) * 4, ' ')||dpt.name||' '||pjt.name
2 from departments dpt
3 , projects pjt
4 where pjt.dpt_id(+) = dpt.id
5 connect by dpt.parent_id = prior dpt.id
6 start with dpt.parent_id is null
7 order siblings by dpt.name
8 , pjt.name;
LPAD('',(LEVEL-1)*4,'')||DPT.NAME||''||PJT.NAME
------------------------------------------------------------------------------------
DEPARTMENT1
DEPARTMENT2 PROJECT1
DEPARTMENT2 PROJECT2
DEPARTMENT3
DEPARTMENT4 PROJECT3[/code]
|
[Updated on: Thu, 01 May 2008 08:24] Report message to a moderator
|
|
|
|
|
|
Re: Master Detail with Connect by [message #317475 is a reply to message #317472] |
Thu, 01 May 2008 09:12   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
There was no need for him to post a testcase, since he clearly stated his desired output based on my testcase.
Nothing to format, since it contains neither code, nor a column-based output.
|
|
|
Re: Master Detail with Connect by [message #317480 is a reply to message #317475] |
Thu, 01 May 2008 09:21   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Not sure if this covers 100% but it might get you back on track:
SQL> select level
2 , name
3 , lpad(' ', (level - 1) * 4, ' ')||name as result
4 from (select id
5 , name
6 , parent_id
7 from departments
8 union all
9 select null
10 , name
11 , dpt_id
12 from projects
13 )
14 connect by prior id = parent_id
15 start with parent_id is null;
LEVEL NAME RESULT
---------- -------------------- --------------------
1 DEPARTMENT1 DEPARTMENT1
2 DEPARTMENT2 DEPARTMENT2
3 PROJECT1 PROJECT1
3 PROJECT2 PROJECT2
2 DEPARTMENT3 DEPARTMENT3
2 DEPARTMENT4 DEPARTMENT4
3 PROJECT3 PROJECT3
[Edit: added indented result to query]
[Updated on: Thu, 01 May 2008 09:24] Report message to a moderator
|
|
|
Re: Master Detail with Connect by [message #317481 is a reply to message #317480] |
Thu, 01 May 2008 09:26   |
rlockard
Messages: 19 Registered: April 2008 Location: Baltimore, MD
|
Junior Member |
|
|
Frank,
By George I think you got it. I really appreciate the effort. I hate that feeling of being stuck. Your solution is simple and elegant.
And I have learned about the Ignore user button. Over time that should improve the noise to signal ratio.
-Rob
|
|
|
Re: Master Detail with Connect by [message #317482 is a reply to message #317481] |
Thu, 01 May 2008 09:30  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Cool!
I continued testing it and it also seems to work with deeper levels of nesting.
Not sure about the consistency of ordering though, but hey, we need to keep something for you to do
|
|
|
Goto Forum:
Current Time: Wed Feb 19 06:01:20 CST 2025
|