473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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(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
Jul 19 '05 #1
2 11488
sr***********@yahoo.com (sreddy) wrote in message news:<f7**************************@posting.google. 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.
Jul 19 '05 #2
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

Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: ensnare | last post by:
I'm attempting to create a threaded comment system involving PHP / MySQL. Currently, I have a table called comments which looks like: Table Comments: (comment_id, comment_root_id,...
21
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All...
4
by: Denis St-Michel | last post by:
Hello All, Hope some Guru will be able to help me with this. Let's take this example table A ------------------------------------------------------------------------------- id | ...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
2
by: marco | last post by:
Dear List, as it seems, MS SQL as used in Access does not allow a select INTO within a UNION query. Also, it seems that a UNION query can not be used as a subquery. Maybe my (simplified)...
1
by: suslikovich | last post by:
Hi all, I am getting this error when insert values from one table to another in the first table the values are varchar (10). In the second they are datetime. The format of the data is mm/dd/yyyy...
12
by: =?Utf-8?B?Smlt?= | last post by:
I have code that reads and parses a text file using a schema.ini file. Works great. When I see the dataGrid it's exactly what I want. Dim CString As String = _...
2
by: sreddy | last post by:
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...
1
by: Rudolf Bargholz | last post by:
Hi, We have created triggers to log modifications to tables in our application. The triggers work fine, just on one of the tables in our database the triggers fail with the error message...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.