How to write the select query on self referencing table.

[sreddy]

I am trying to write a sql query on self referencing table.
Just to brief ..Database is related to a Hiring department of the
Qwest company.

I need to generate a Report used by in HR department to pay the
employees
who have referred the candidates for the jobs in their

..
This report is used by the HR department to get the required
information of all the
candidates(incl udes parent and child information in which parent is
quest employee
and child is the candidate who have been referred) in a given time
frame

Referral_nodes table is a self referencing tables in which
parent_node_id is the id value of the parent and just ID
is the id value of the child so i guess in the query i need to use
CONNECT BY PRIOR to connect the child records
to the related parent and get the job(from table called job) and
personl(from table called person)
information for both the parent and child
Just to get an idea.. i have attached a dummy report to email
....hope it will give you the clear idea of what i am tring to do.

Referral_nodes: PARENT_NODE_ID is referenced to ID
Name Null? Type
----------------------------------------- --------
----------------------------
ID NOT NULL NUMBER(38)
REFERRER_ID NUMBER(38)
RECEIVER_ID NUMBER(38)
CONTACT_ID NUMBER(38)
JOB_ID NUMBER(38)
PARENT_NODE_ID NUMBER(38)
TYPE NUMBER(38)
STATUS NUMBER(38)
CREATED_ON DATE
MODIFIED_ON DATE

SQL> desc job
ID NOT NULL NUMBER(38)
DIVISION NUMBER(38)
ADMIN NUMBER(38)
NAME VARCHAR2(80)
TITLE VARCHAR2(255)

SQL> desc person;
Name Null? Type
----------------------------------------- --------
--------------------------
ID NOT NULL NUMBER(38)
CORR_INFO NUMBER(38)
LOGIN_ID VARCHAR2(50)
FIRST_NAME VARCHAR2(50)
SECOND_NAME VARCHAR2(50)
LAST_NAME VARCHAR2(50)

Relations

referral_nodes. job_id = job.id
referral_nodes. referrer_id = person.id
referral_nodes. receiver_id = person.id

As a query out put i need get the following columns as output

Job Name: name column from job table
Job Title: job column from job table
Referred to email: login_id column from person table
Referred to Name: first_name,last _name columns from person table
Trusted Referrer email(qwest employee): login_id column from person
table
Trusted Referrer name(qwest employee): first_name,last _name columns
from person table
Date Referred: created_on column from referral table

[Ed prochak]

sr***********@y ahoo.com (sreddy) wrote in message news:<f7******* *************** ****@posting.go ogle.com>...

I am trying to write a sql query on self referencing table.
Just to brief ..Database is related to a Hiring department of the
Qwest company.

Brief hint: consider the referal_nodes table to be two tables, one for
the employee, one for the referred by employee.

Ed Prochak
Magic Interface, Ltd.

[Daniel Roy]

Hint 2: Your assumption that you should use CONNECT BY PRIOR is wrong.
This is not a hierarchy (if my boss referred one of my employees, it
doesn't mean that I also referred him!).

Daniel

I am trying to write a sql query on self referencing table.
Just to brief ..Database is related to a Hiring department of the
Qwest company.

I need to generate a Report used by in HR department to pay the
employees
who have referred the candidates for the jobs in their

.
This report is used by the HR department to get the required
information of all the
candidates(incl udes parent and child information in which parent is
quest employee
and child is the candidate who have been referred) in a given time
frame

Referral_nodes table is a self referencing tables in which
parent_node_id is the id value of the parent and just ID
is the id value of the child so i guess in the query i need to use
CONNECT BY PRIOR to connect the child records
to the related parent and get the job(from table called job) and
personl(from table called person)
information for both the parent and child
Just to get an idea.. i have attached a dummy report to email
...hope it will give you the clear idea of what i am tring to do.

Referral_nodes: PARENT_NODE_ID is referenced to ID
Name Null? Type
----------------------------------------- --------
----------------------------
ID NOT NULL NUMBER(38)
REFERRER_ID NUMBER(38)
RECEIVER_ID NUMBER(38)
CONTACT_ID NUMBER(38)
JOB_ID NUMBER(38)
PARENT_NODE_ID NUMBER(38)
TYPE NUMBER(38)
STATUS NUMBER(38)
CREATED_ON DATE
MODIFIED_ON DATE

SQL> desc job
ID NOT NULL NUMBER(38)
DIVISION NUMBER(38)
ADMIN NUMBER(38)
NAME VARCHAR2(80)
TITLE VARCHAR2(255)

SQL> desc person;
Name Null? Type
----------------------------------------- --------
--------------------------
ID NOT NULL NUMBER(38)
CORR_INFO NUMBER(38)
LOGIN_ID VARCHAR2(50)
FIRST_NAME VARCHAR2(50)
SECOND_NAME VARCHAR2(50)
LAST_NAME VARCHAR2(50)

Relations

referral_nodes. job_id = job.id
referral_nodes. referrer_id = person.id
referral_nodes. receiver_id = person.id

As a query out put i need get the following columns as output

Job Name: name column from job table
Job Title: job column from job table
Referred to email: login_id column from person table
Referred to Name: first_name,last _name columns from person table
Trusted Referrer email(qwest employee): login_id column from person
table
Trusted Referrer name(qwest employee): first_name,last _name columns
from person table
Date Referred: created_on column from referral table