Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Data
Hierarchical Data [message #197241] Tue, 10 October 2006 13:38 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I have the following table and insert script. Here for eg,

I want to display all records of NANCY WALTER.
The c_MGRID is blank for 2nd and 3rd record of NANCY WALTER.

create table EMPINFO
( c_STATUS VARCHAR2(2),
c_GROUP VARCHAR2(10),
c_SYSID VARCHAR2(10),
c_SYSTEM VARCHAR2(10),
c_NAME VARCHAR2(40),
c_ID VARCHAR2(10),
c_MGRID VARCHAR2(10),
c_MGRNAME VARCHAR2(40)
);

Typical Data is
insert into EMPINFO VALUES ('A','HR','','','JASON BICKER','0000001','','');
insert into EMPINFO VALUES ('A','HR','','','NANCY WALTER','0400001','0000001','JASON BICKER');
insert into EMPINFO VALUES ('','MF','NAN3005','WIN','NANCY WALTER','0400001','','');
insert into EMPINFO VALUES ('','MF','NAN3005','UNIX','NANCY WALTER','0400001','','');
insert into EMPINFO VALUES ('A','HR','','','ADAM BRYAN','0400002','0000001','JASON BICKER');
insert into EMPINFO VALUES ('','MF','BRY0034','WIN','ADAM BRYAN','0400002','','');
insert into EMPINFO VALUES ('A','HR','','','STING CORY','0400003','0400002','ADAM BRYAN');
insert into EMPINFO VALUES ('','MF','STI4040','UNIX','STING CORY','0400003','','');


start with and connect by prior only displays the 1st row because there is a relationship between mgr id and emp id.
Re: Hierarchical Data [message #197249 is a reply to message #197241] Tue, 10 October 2006 14:04 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure is this what you are looking for:
SQL> column c_mgrname format a15
SQL> column c_name format a15
SQL>
SQL> SELECT c_mgrname, c_name, c_status, c_group, c_sysid, c_system
  2  FROM EMPINFO
  3  CONNECT BY PRIOR c_id = c_mgrid
  4  START WITH c_id = '0400001';

C_MGRNAME       C_NAME          C_ C_GROUP    C_SYSID    C_SYSTEM
--------------- --------------- -- ---------- ---------- ----------
JASON BICKER    NANCY WALTER    A  HR
                NANCY WALTER       MF         NAN3005    WIN
                NANCY WALTER       MF         NAN3005    UNIX

SQL>
Of course, such a trivial query will also return all records of NANCY WALTER, but this almost surely isn't the solution to the problem:
SELECT * FROM EMPINFO 
WHERE c_name = 'NANCY WALTER';
Re: Hierarchical Data [message #197251 is a reply to message #197249] Tue, 10 October 2006 14:16 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Well I need a similar output for all employees reporting to their respective managers starting with JASON BICKER. If i provide the following query.]


SELECT c_mgrname, c_name, c_status, c_group, c_sysid, c_system FROM EMPINFO
START WITH c_id = '0000001' CONNECT BY PRIOR c_id = c_mgrid;

The O/p is as below. In this case only one record of NANCY WALTER comes up. Similar only one record of Adam Bryan comes even though the table contains 2 records.


C_MGRNAME C_NAME C_ C_GROUP C_SYSID C_SYSTEM
--------------- --------------- -- ---------- ---------- ----------
JASON BICKER A HR
JASON BICKER NANCY WALTER A HR
JASON BICKER ADAM BRYAN A HR
ADAM BRYAN STING CORY A HR
Previous Topic: Return XML from the database
Next Topic: Case Statement
Goto Forum:
  


Current Time: Wed Dec 07 02:44:16 CST 2016

Total time taken to generate the page: 0.14369 seconds