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
2  11536  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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 query calls. This is all to
the good; however, with lengthy discussion, this solution becomes slow
and...
|
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 "<stdin>", line 1, in ?
|
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 9 Jamie
2 1 8.5 ...
|
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)
|
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.
| | |
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 Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT ...
|
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)
|
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
|
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 triggers are autogenerated with the
same structure for most of the tables used in our application).
I...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
