Get data from another table using multiple join

Question

Here, I need data 'Eac_Card_Number' from another table 'Acc_Emp_AccessCard acc' where emp.emp_id = acc.Eac_EmpId and max(acc.Eac_startDate).

How to integrate this condition in below query:

Select CASE 
          WHEN gen.Category_Name = 'Male' THEN 'Mr' 
          WHEN gen.Category_Name = 'Female' THEN 'Ms' 
       END as Emp_Salutation 
      ,l.Login_Id as LgInfo_Nt_Login_Id 
      ,org.Category_Name as emp_Organisation 
      ,gen.Category_Name as emp_gender_id  
      ,emp.Emp_category 
      ,emp.emp_id
from prj_employee_detail emp 
     left join prj_category gen on emp.emp_gender_id = gen.Category_Id  
     left join prj_category org on emp.emp_Organisation = org.Category_Id 
     left join Prj_Login_Info l on emp.emp_id=l.LgInfo_Resource_Id  
where l.lginfo_client_id = 0  and emp.emp_number not in ('0','')

Reza · Accepted Answer · 2014-04-11 09:22:32Z

2

You can join using drived table like this

Select CASE 
          WHEN gen.Category_Name = 'Male' THEN 'Mr' 
          WHEN gen.Category_Name = 'Female' THEN 'Ms' 
       END as Emp_Salutation 
      ,l.Login_Id as LgInfo_Nt_Login_Id 
      ,org.Category_Name as emp_Organisation 
      ,gen.Category_Name as emp_gender_id  
      ,emp.Emp_category 
      ,emp.emp_id
      ,acc.Eac_Card_Number
from prj_employee_detail emp 
     left join prj_category gen on emp.emp_gender_id = gen.Category_Id  
     left join prj_category org on emp.emp_Organisation = org.Category_Id 
     left join Prj_Login_Info l on emp.emp_id=l.LgInfo_Resource_Id  
     left outer join (select Row_Number() over(partition by Eac_EmpId order by Eac_startDate desc) rn, Eac_EmpId,Eac_Card_Number  from Acc_Emp_AccessCard ) acc on acc.EAC_EmpID = emp.emp_id and acc.rn=1 
where l.lginfo_client_id = 0  and emp.emp_number not in ('0','')

edited Apr 11, 2014 at 9:22

answered Apr 11, 2014 at 7:17

Reza

20.1k18 gold badges102 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CodeManiac Over a year ago

how to select acc.Eac_Card_Number

Reza Over a year ago

@CodeManiac is Card_Number unique for every emp in Acc_Emp_AccessCard?

Reza Over a year ago

@CodeManiac if yes you can either add it in group by and select clause or takeing Max(EAX_Card_Number) in select clause.

CodeManiac Over a year ago

Card Number is not unique. I have to select eac_card_number which have max eac_startdate of specific emp_id

Sander · Accepted Answer · 2014-04-11 07:25:23Z

Did you try a subquery in the select-statement? That would be my first guess:

Select CASE 
          WHEN gen.Category_Name = 'Male' THEN 'Mr' 
          WHEN gen.Category_Name = 'Female' THEN 'Ms' 
       END as Emp_Salutation 
      ,l.Login_Id as LgInfo_Nt_Login_Id 
      ,org.Category_Name as emp_Organisation 
      ,gen.Category_Name as emp_gender_id  
      ,emp.Emp_category 
      ,emp.emp_id
      ,(select Eac_Card_Number from Acc_Emp_AccessCard acc
         where emp.emp_id = acc.Eac_EmpId and max(acc.Eac_startDate)) as card_number
from prj_employee_detail emp 
     left join prj_category gen on emp.emp_gender_id = gen.Category_Id  
     left join prj_category org on emp.emp_Organisation = org.Category_Id 
     left join Prj_Login_Info l on emp.emp_id=l.LgInfo_Resource_Id  
where l.lginfo_client_id = 0  and emp.emp_number not in ('0','')

Collectives™ on Stack Overflow

Get data from another table using multiple join

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related