oracle - SQL join by combination of ID and date range -

there tables - assignments , countries.

assignments stores historical data employees historical assignments, , 3 main fields - person_id, effective_start_date , effective_end_date.

countries stores info employees have been taking trips abroad - important fields in person_id, date_from, date_to, home_country, host_country.

and need following : need show assignments, , country employee had been @ point during assignment. means need join them via outer join, way can join them via person_id there multiple entries in each table (same id's)

did :

select *  assignments ass, employees emp ass.person_id=emp.person_id , (emp.date_from(+) >= ass.assignment_start_date , emp.date_to(+) <= ass.assignment_end_date) or (emp.date_from(+) >= ass.assignment_start_date , emp.date_to(+) >= ass.assignment_end_date)  

but doesnt work because oracle doesn't allow me make or statement in outer join. tried using union method values returned not quite expected - there missing values, logic isn't correct eighter. if have advices please post them in same syntax provided (oracle syntax) joins made in clause, it's easier me understand.

to desired result, can use following approach:

• use ansi style joins instead of outdated oracle syntax (they're more flexible , imho more readable)
• concatenate countries (e.g. using listagg)

query:

select ass.person_id,         assignment_start_date,         assignment_end_date,           listagg(home_country ||'-' || host_country, ';')           within group (order date_from) countries   assignments ass   left join employees emp     on ass.person_id = emp.person_id    , ((emp.date_from >= ass.assignment_start_date ,        emp.date_from <= ass.assignment_end_date)     or (emp.date_to >= ass.assignment_start_date ,        emp.date_to <= ass.assignment_end_date)) group ass.person_id, assignment_start_date, assignment_end_date 

sql fiddle