Dispaly of data from query

[Former Member]

I have two tables,

Table1 : EMployee_details

emp_id

first_name

last_name

Table2: Leaves_list

applicant_id

from_dt

to_dt

TL_id

DPM_id

PM_id

Data:

EMployee_details

101 abc

102 def

103 ghi

104 jkl

Leave_list

101 2011-01-01 2011-01-02 102 103 104

Now, in my report i should display all leave records as,

Applicant_name From date TO date TL Name DPM Name PM Name

abc 2011-01-01 2011-01-02 def ghi jkl

I wrote a query like,

Select concat(e.first_name,' ',e.last_name),l.from_dt,l.to_dt,l.tl_id,l.dpm_id,l.pm_id from employee_details e, leaves_list l where e.emp_id=l.emp_id

I am able to display the Applicant name. But how to display the TL, DPM, PM names in the report?

Can anyone please help me out?

Thank you.

[Former Member · Accepted solution]

I have rewritten your query with ANSI syntax which is easier to follow, but this should work

Select concat(e.first_name,' ',e.last_name)emp_name,l.from_dt,l.to_dt,l.tl_id,l.dpm_id,l.pm_id,

concat(tl.first_name,' ',tl.last_name) tl_name,

concat(dpm.first_name,' ',dpm.last_name)dpm_name,

concat(pm.first_name,' ',pm.last_name) pm_name

from employee_details e

inner join leaves_list l on e.emp_id=l.emp_id

inner join employee_details tl on l.tl_id = tl.emp_id

inner join employee_details dpm on l.dpm_id = dpm.emp_id

inner join employee_details pm on l.pm_id = pm.emp_id

Ian

[Former Member · Accepted solution]

Thank you so much it is working. I have one more requirement,

TABLE 3:

Leave_Status_list

Status_id Status_name

1 Pending

2 Approved

3 Rejected

In leaves_list tl_id,dpm_id,pm_id will be there, how to rewrite the query such that TL,DPm,PM status names be displayed?

Thank you

[Former Member · Accepted solution]

I am not getting the data properly. In the leaves_list some of the records will have TL_id & some wont have. Only PM_id is mandatory. IF TL or DPM ids are null means the query is not returning those records. How to retrieve those records also?

Thank you

[Former Member · Accepted solution]

When you join in the leave status table you will need to alias the employee table again for each connection from the status table.

If links are not compulsory change joins to left outer

Select concat(e.first_name,' ',e.last_name)emp_name,l.from_dt,l.to_dt,l.tl_id,l.dpm_id,l.pm_id,

concat(tl.first_name,' ',tl.last_name) tl_name,

concat(dpm.first_name,' ',dpm.last_name)dpm_name,

concat(pm.first_name,' ',pm.last_name) pm_name

from employee_details e

inner join leaves_list l on e.emp_id=l.emp_id

left outer join employee_details tl on l.tl_id = tl.emp_id

left outer join employee_details dpm on l.dpm_id = dpm.emp_id

left outer join employee_details pm on l.pm_id = pm.emp_id

This will bring back all data even if there are nulls in the aliased employee details table

Ian

[Former Member · Accepted solution]

I am not getting the exact data, tables data is as below,

Employee_details

emp_id Emp_name

101 abc

102 def

103 ghi

104 jkl

Leaves_list

applicant_id from_dt to_dt TL_id DPM_id PM_id TL_Status DPM_Status PM_Status

abc 2011-01-01 2011-01-02 102 103 104 1 2 1

def 2011-01-04 2011-01-04 - 103 104 1 1 1

Leave_status_list

Status_id Status_name

1 Pending

2 Approved

3 Rejected

Query should display,

abc 2011-01-01 2011-01-02 def Pending ghi Approved jkl Pending

def 2011-04-04 2011-01-04 ghi Pending jkl Pending

[Former Member · Accepted solution]

Please show both your query and what data you are getting back.

Ian

[Former Member · Accepted solution]

select concat(e.First_Name,' ',e.Last_Name),l.Start_dt,l.End_dt,

concat(tl.First_Name,' ',tl.Last_Name) as 'TL',

concat(dpm.First_Name,' ',dpm.Last_Name) as 'DPM',

concat(pm.First_Name,' ',pm.Last_Name) as 'PM',l.List_type_id

from employee_details e

inner join leaves_applied_list l on e.Emp_Id=l.Emp_Id

inner join employee_details tl on tl.Emp_Id=l.T1_Id

inner join employee_details dpm on dpm.Emp_Id=l.T2_Id

inner join employee_details pm on pm.Emp_Id=l.T3_Id

and "2011-08-23"> l.End_dt

I need the data to be filtered based on above condition, but i am getting all the records from database.

Database records,

abc 2011-01-01 2011-01-02 def Pending ghi Approved jkl Pending

def 2011-04-04 2011-01-04 ghi Pending jkl Pending

abc 2011-09-09 2011-09-09 def Pending ghi Pending JKL Pending

Required Query output,

abc 2011-01-01 2011-01-02 def Pending ghi Approved jkl Pending

def 2011-04-04 2011-01-04 ghi Pending jkl Pending

Current Query output,

abc 2011-01-01 2011-01-02 def Pending ghi Approved jkl Pending

def 2011-04-04 2011-01-04 ghi Pending jkl Pending

abc 2011-09-09 2011-09-09 def Pending ghi Pending JKL Pending

[Former Member · Accepted solution]

Your filter clause needs to be WHERE

select concat(e.First_Name,' ',e.Last_Name),l.Start_dt,l.End_dt,

concat(tl.First_Name,' ',tl.Last_Name) as 'TL',

concat(dpm.First_Name,' ',dpm.Last_Name) as 'DPM',

concat(pm.First_Name,' ',pm.Last_Name) as 'PM',l.List_type_id

from employee_details e

inner join leaves_applied_list l on e.Emp_Id=l.Emp_Id

inner join employee_details tl on tl.Emp_Id=l.T1_Id

inner join employee_details dpm on dpm.Emp_Id=l.T2_Id

inner join employee_details pm on pm.Emp_Id=l.T3_Id

WHERE l.End_dt < "2011-08-23"

In ANSI syntax your AND was only applied to the last table join

Ian

[Former Member · Accepted solution]

select concat(e.First_Name,' ',e.Last_Name),l.Start_dt,l.End_dt,

concat(tl.First_Name,' ',tl.Last_Name) as 'TL',l.t1_approval_status_id,

concat(dpm.First_Name,' ',dpm.Last_Name) as 'DPM',l.t2_approval_status_id,

concat(pm.First_Name,' ',pm.Last_Name) as 'PM',l.T3_Approval_Status_Id,l.List_type_id

from employee_details e

inner join leaves_applied_list l on e.Emp_Id=l.Emp_Id

left outer join employee_details tl on tl.Emp_Id=l.T1_Id

left outer join employee_details dpm on dpm.Emp_Id=l.T2_Id

left outer join employee_details pm on pm.Emp_Id=l.T3_Id

where l.End_dt < "2011-08-23"

It is working now, but i am not knowing how to dispaly pending, approved, rejected status?

[Former Member · Accepted solution]

It will be something like

select concat(e.First_Name,' ',e.Last_Name),l.Start_dt,l.End_dt,

concat(tl.First_Name,' ',tl.Last_Name) as 'TL',l.t1_approval_status_id,

concat(dpm.First_Name,' ',dpm.Last_Name) as 'DPM',l.t2_approval_status_id,

concat(pm.First_Name,' ',pm.Last_Name) as 'PM',l.T3_Approval_Status_Id,l.List_type_id,

stl.Status_name as TL_Status,

sdpm.Status_name as DPM_Status,

spm.Status_name as PM_Status

from employee_details e

inner join leaves_applied_list l on e.Emp_Id=l.Emp_Id

left outer join employee_details tl on tl.Emp_Id=l.T1_Id

left outer join employee_details dpm on dpm.Emp_Id=l.T2_Id

left outer join employee_details pm on pm.Emp_Id=l.T3_Id

left outer join Leave_status_list stl on l.TL_Status = stl.status_id

left outer join Leave_status_list sdpm on l.DPM_Status = sdpm.status_id

left outer join Leave_status_list spm on l.PM_Status = spm.status_id

where l.End_dt < "

Ian

[Former Member · Accepted solution]

Thank you so much, it is working now