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(includes 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