How to write the select query on self referencing table.

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • sreddy
    #1

    How to write the select query on self referencing table.

    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



    SQLdesc job


    ID NOT NULL NUMBER(38)
    DIVISION NUMBER(38)
    ADMIN NUMBER(38)
    NAME VARCHAR2(80)
    TITLE VARCHAR2(255)

    SQLdesc 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
    Tags: None
    • Ed prochak
      #2
      Re: How to write the select query on self referencing table.

      sreevennreddy@y ahoo.com (sreddy) wrote in message news:<f7d639cb. 0309081103.63c6 1c39@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.

        Comment

        • Daniel Roy
          #3
          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(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
          >
          >
          >
          SQLdesc job
          >
          >
          ID NOT NULL NUMBER(38)
          DIVISION NUMBER(38)
          ADMIN NUMBER(38)
          NAME VARCHAR2(80)
          TITLE VARCHAR2(255)
          >
          SQLdesc 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

            Comment

            Previous template Next
            Working...