Bdtjtk

I have 2 tables below

Paam Table

AssignmentID     PersonID         AssignmentType

300000014199240  300000014199145  E

300000014199174  300000014199145  ET



Par Table

ASGResponsID    AssignmentID    PersonID        Responsibility_Type

300000015244074 300000014199240 300000014199145 RC_HR_BP

300000015242351 300000014199240 300000014199145 RC_HR_BP

300000015244070 300000014199240 300000014199145 RC_HR_BP

I want to join these 2 tables and get output like below

PersonID         Responsiblity_Type

300000014199145  RC_HR_BP

I am using the below query

select 

par.PersonID, par.Responsibility_Type 

from 

per_all_assignments_m paam, per_asg_responsibilities par

where

sysdate between nvl(paam.effective_start_date,sysdate) and 

nvl(paam.effective_end_date,sysdate)

and  paam.assignment_type='E'

and paam.assignment_id = par.assignment_id(+)

and paam.person_id = '300000014199145';

Instead I am getting output like below

PersonID         Responsiblity_Type

300000014199145  RC_HR_BP

300000014199145  RC_HR_BP

300000014199145  RC_HR_BP

In spite of using left outer join I am getting multiple rows, why is this happening?

Can someone please help me understand ?

Thanks,
Shivam

Start by using a regular join with proper syntax:

select par.PersonID, par.Responsibility_Type 

from per_all_assignments_m paam join

     per_asg_responsibilities par

     on paam.assignment_id = par.assignment_id and

        paam.person_id = par.person_id  -- presumably also needed for the join

where sysdate between nvl(paam.effective_start_date, sysdate) and 

nvl(paam.effective_end_date, sysdate) and

      paam.assignment_type = 'E' and

      paam.person_id = '300000014199145';

Your intention with the outer join might be:

select par.PersonID, par.Responsibility_Type 

from per_asg_responsibilities par left join

     per_all_assignments_m paam

     on paam.assignment_id = par.assignment_id and

        paam.person_id = par.person_id  and

        sysdate between nvl(paam.effective_start_date, sysdate) and 

nvl(paam.effective_end_date, sysdate) and

        paam.assignment_type = 'E' and

where par.person_id = '300000014199145';

Based on your data, you're getting the correct results. All three rows in the par table have the same assignmentId (300000014199240) so will be returned by your query regardless of the join syntax.

Adding a "Distinct" clause will get you the results you want.

select DISTINCT

  par.PersonID, par.Responsibility_Type 

from 

  per_all_assignments_m paam, per_asg_responsibilities par

where

  sysdate between nvl(paam.effective_start_date,sysdate) and 

  nvl(paam.effective_end_date,sysdate)

  and  paam.assignment_type='E'

  and paam.assignment_id = par.assignment_id(+)

  and paam.person_id = '300000014199145';

I set this up in a SQL Fiddle here http://sqlfiddle.com/#!4/d7b7f/8. Note: I removed the date field references since you didn't give us data for those, but it shouldn't affect the results based on how you described the problem.