Oracle Query on 3 tables with 2 outer joins

Question

persons - a table of user names:

per_id      number(10) - primary key, populated by a sequence
user_name   varchar2(50)
user_id     varchar2(15) - unique, basically the employee ID

work_assignments - like crew assignments, but more general:

wa_id     number(10) - primary key, populated by a sequence
wa_name   varchar2(25)

current_assignments - which users have which work_assignments; the average per user is about 25 work assignments, but some individuals have upwards of 150:

wa_id   number(10)
per_id  number(10)

I'm trying to write a query that will compare the work_assignments for two users, in a total of three columns. The results should look like this:

WA_Name   User_Name1    User_Name2
Crew A    Bob           Joe
Crew B                  Joe
Crew C    Bob

That is, every work_assignment that either of the two user has, with the name(s) of the user(s) who has it.

select distinct * from (
  select wa.name      work_assignment,
         per.name     user_name1,
         per2.name    user_name2
    from work_assignments  wa join current_assignments ca  on wa.wa_id = ca.wa_id
                              join current_assignments ca2 on wa.wa_id = ca2.wa_id
                   left outer join persons per  on per.per_id  = ca.per_id  and per.user_id  = 'X12345'
                   left outer join persons per2 on per2.per_id = ca2.per_id and per2.user_id = 'Y67890'
                   )
   where user_name1 is not null or user_name2 is not null
   order by 1;

The problem is that if both users have a work assignment, it shows 3 records: one for Bob, one for Joe, and one for both:

WA_Name   User_Name1    User_Name2
Crew A    Bob           Joe
Crew A                  Joe
Crew A    Bob

Answer 1

I created a set of sample data/tables

drop table persons;
drop table work_assgn;
drop table curr_assgn;


create table persons(
  per_id number(10) not null
, user_name varchar2(10) not null
, user_id varchar2(10) not null
)
;

insert into persons values( 1, 'Bob', 'X123' );
insert into persons values( 2, 'Joe', 'Y456' );
insert into persons values( 3, 'Mike', 'Z789' );
insert into persons values( 4, 'Jeff', 'J987' );

commit;

create table work_assgn(
  wa_id number(10) not null
, wa_name varchar2(25)
)
;

insert into work_assgn values( 10, 'Crew A' );
insert into work_assgn values( 20, 'Crew B' );
insert into work_assgn values( 30, 'Crew C' );
insert into work_assgn values( 40, 'Crew D' );

commit;

create table curr_assgn(
  wa_id number(10) not null
, per_id number(10) not null
)
;

insert into curr_assgn values( 10, 1 );
insert into curr_assgn values( 10, 2 );
insert into curr_assgn values( 20, 2 );
insert into curr_assgn values( 30, 1 );
insert into curr_assgn values( 40, 4 );

commit;


select * from persons;
select * from work_assgn;
select * from curr_assgn;

So the data looks like

PERSONS
    PER_ID USER_NAME  USER_ID
---------- ---------- ----------
         1 Bob        X123
         2 Joe        Y456
         3 Mike       Z789
         4 Jeff       J987


WORK_ASSGN
     WA_ID WA_NAME
---------- -------------------------
        10 Crew A
        20 Crew B
        30 Crew C
        40 Crew D

CURRASSGN
     WA_ID     PER_ID
---------- ----------
        10          1
        10          2
        20          2
        30          1
        40          4

One approach may be to use a PIVOT

with assignment as
(
select  p.user_id, p.user_name, a.wa_name
from persons p
join curr_assgn c
  on p.per_id =c.per_id
join work_assgn a
  on a.wa_id = c.wa_id
where p.user_id in ( 'X123', 'Y456' )
)
select * from assignment
pivot
( max(user_name) for user_id in ( 'X123', 'Y456' )
)
;