Re: How to write the select query on self referencing table.

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(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
Received on Wed Sep 10 2003 - 17:50:21 CEST