Skip to Content
avatar image
Former Member

Join help

Hi All,

Please help me on this requirement.

I have a table pa_master_details and lov_details.


In the pa_master_details table, I have role_comp_emp_final_rating and role_comp_lm_final_rating columns.

In the lov_details table I have a column rating lov_value and lov_text_en.

I need to like this

EmpNo   EmpRating      LMRating     Emptext     LMText

I need to join role_comp_emp_final_rating with the lov_value column to get lov_text_en Based on this lov_value, I need to show lov_text_en from the lov_details table.

So I wrote a query like this and am getting the result for emp rating:

SELECT p.employee_number,

p.role_comp_emp_final_rating,

lov_text_en,

p.role_comp_lm_final_rating

FROM pa_master_details P, lov_details L

WHERE p.role_comp_emp_final_rating = l.lov_value

AND p.employee_number = 34570

Similarly, I need to show lov_text_en for role_comp_lm_final_rating from the lov_details table by joining role_comp_lm_final_rating with lov_value in the same query.

How do I do it?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 06, 2015 at 07:44 AM

    Hi,

    So, you have a common table lov_details and you want to join it with 2 tables  role_comp_emp_final_rating and role_comp_lm_final_rating, right?

    Does role_comp_emp_final_rating and role_comp_lm_final_rating have same columns?


    Do you need to differentiate the records retrieved from these tables?


    You can use a union with your existing query get the required records from both tables.


    Let me know if that works out.


    Thanks,

    Raghavendra

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06, 2015 at 02:35 PM

    Instead of using UNIONs, you might also be able to do this by using multiple joins to the LOV_Details table using aliases.  It would look something like this:

      SELECT p.employee_number,

      p.role_comp_emp_final_rating,

      Lemp.ov_text_en empText,

      p.role_comp_emp_final_rating

      Lrating.ov_text_en ratingText

      FROM pa_master_details P

      Inner Join lov_details Lemp

      ON p.role_comp_emp_final_rating = Lemp.lov_value

      inner join lovdetails Lrating

      on p.role_comp_lm_final_rating = Lrating.lov.value

    -Dell

    Add comment
    10|10000 characters needed characters exceeded