473,555 Members | 2,402 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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 11512
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.
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(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

Jul 19 '05 #3

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

Similar topics

5
5186
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, comment_parent_id, text, datetime_created). Most of the tutorials that I've found online discuss how to thread the results from the database with recursive...
21
5224
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 works as expected. But >>> numb=10 >>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb) Traceback (innermost last): File...
4
1371
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 | TicketNo | evaluation | Username ------------------------------------------------------------------------------- 1 1 ...
17
4977
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 cust_no, ded_type_cd, chk_no)
2
9750
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) problem can avoid these technicalities: the original table has columns A1, A2, B1, B2, C1, C2.
1
5464
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 to be easily converted to dates. The conversion in this case is implicit as indicated in SQL Server documentation. Here is my query: INSERT INTO...
12
2085
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 = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\;" & _ "Extended Properties=""text;HDR=No;""" Dim TConnect As New System.Data.OleDb.OleDbConnection(CString)
2
401
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 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
1
15910
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 <SQL0437W Performance of this complex query may be sub-optimal. Reason code: "1". SQLSTATE=01602>. The same trigger on other tables works fine (the...
0
7542
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7824
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7587
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5452
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3593
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3573
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2034
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1156
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
863
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.