Replying to the user list - this is not appropriate for the dev list.
When you do a self join, you will need to rename some of the columns so they are unique.  For example:
select, as boss_name
from employees a join employees b
on a. boss_id = b.employee_id
This will also mean that you cannot reuse the result maps as you are attempting to do.
Another option is to not rename the columns, and use column indexes in your result maps instead of column names.  But again, you will need two different result maps.  And I would strongly advise against using column indexes when using "select *".
Jeff Butler

On Tue, Apr 1, 2008 at 6:03 AM, Rajive <> wrote:

Hi all,

Please help in solving this problem.

I have a employeeprofile table in which all employee details are stored .
but it refers to other tables like appointment status , designation table .
So employee profile  table contains IDs which refer to corresponding  status
& designation table to fetch Objects of status and designation .
TiLL now it works fine .

But i have an other constraint i.e Authorized signatory(u can think this as
reporting manager) which refers to Emp Code in same table (employee

So like the way i achieved results for status and designation objects
(Information from their respective tables for the selected record in form of
Objects) and it also worked fine .But when i try to obtain auth signatory
details it does not work.

What i have done is i have created a Employee profile BO inside  Employee
profile BO(Contains relationship)
(same way i had created for status and designation and it was working fine).

wat i obatain as result is all data filled with status BO ,Designation BO ,
But in Child Employee profile BO data comes as that of parent itself not
that of signatory.

Heres a mapping snap :
the code in bold and italic i have added to get signatory details .. else
every thing is working fine.

also i have tested the qurey .. query is fetching me results.

I think  problem is in the mapping OR i DONT KNOW WAT TO DO.

<sqlMap namespace="report">

       <resultMap class=""
               <result property="appointmentStatus"
                       resultMap="tblAppointmentStatus.appointmentStatusBOResult" />
               <result property="designation"
                       resultMap="tblDesignation.designationBOResult" />
              <result property="authSign"
                       resultMap="tblEmployeeProfile.employeeProfileBOResult" />

<select id="get_report_by_joinee_name"
               select * from tblEmployeeProfile EP LEFT OUTER JOIN
               tblAppointmentStatus APP ON EP.emp_appointment_status =
               APP.appoint_stat_no LEFT OUTER JOIN tblDesignation DEG ON
               EP.emp_designation = DEG.designation_no LEFT OUTER JOIN
               tblEmployeeProfile ASEP ON EP.emp_auth_sign=ASEP.emp_OLC where
               (UPPER(EP.emp_fname) LIKE UPPER(#name:VARCHAR#) or
               UPPER(EP.emp_lname) LIKE UPPER(#name:VARCHAR#) or
               UPPER(EP.emp_fname+EP.emp_lname) LIKE UPPER(#name:VARCHAR#))

Please help me with your valuable solutions :)
View this message in context:
Sent from the iBATIS - Dev mailing list archive at