-- #############################################################################################
--
-- %Purpose: How to query a n X m relation using the UNION construct to avoid the ORA-1417 error
--
-- Consider the following situation: We have employees and projects.
--
-- An employee can be registered (work for) in 0, 1or more projects. For a certain project,
-- 0 one or more employees are allocated. We have a typical many-to-many relationship which
-- is normalized with the intersection entity PROJALLOC.
--
-- #############################################################################################
--
CREATE TABLE employee (
 emp_id     NUMBER NOT NULL,
 name       VARCHAR2(30),
 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
);

CREATE TABLE project (
 proj_id    NUMBER NOT NULL,
 name       VARCHAR2(30),
 CONSTRAINT proj_pk PRIMARY KEY (proj_id)
);

CREATE TABLE projalloc (
 emp_id     NUMBER NOT NULL,
 proj_id    NUMBER NOT NULL,
 CONSTRAINT pa_pk  PRIMARY KEY (proj_id, emp_id),
 CONSTRAINT pa_fk1 FOREIGN KEY (proj_id) REFERENCES PROJECT (proj_id),
 CONSTRAINT pa_fk2 FOREIGN KEY (emp_id) REFERENCES EMPLOYEE (emp_id)
);

INSERT INTO employee (emp_id,name) VALUES (1,'ALLEN');
INSERT INTO employee (emp_id,name) VALUES (2,'BAKER');
INSERT INTO employee (emp_id,name) VALUES (3,'FORD');
INSERT INTO employee (emp_id,name) VALUES (4,'MILLER');
INSERT INTO employee (emp_id,name) VALUES (5,'SCOTT');

INSERT INTO project (proj_id,name) VALUES (1,'PROJECT 01');
INSERT INTO project (proj_id,name) VALUES (2,'PROJECT 02');
INSERT INTO project (proj_id,name) VALUES (3,'PROJECT 03');
INSERT INTO project (proj_id,name) VALUES (4,'PROJECT 04');
INSERT INTO project (proj_id,name) VALUES (5,'PROJECT 05');

INSERT INTO projalloc (proj_id,emp_id) VALUES (1,1);
INSERT INTO projalloc (proj_id,emp_id) VALUES (1,2);
INSERT INTO projalloc (proj_id,emp_id) VALUES (1,3);
INSERT INTO projalloc (proj_id,emp_id) VALUES (2,2);
INSERT INTO projalloc (proj_id,emp_id) VALUES (2,5);
INSERT INTO projalloc (proj_id,emp_id) VALUES (3,3);
INSERT INTO projalloc (proj_id,emp_id) VALUES (4,3);
COMMIT;

select e.name employee,p.name name
 from employee e, projalloc pa, project p
where e.emp_id = pa.emp_id(+)
  and p.proj_id = pa.proj_id(+)
order by 1

ORA-01417: a table may be outer joined to at most one other table

--
-- Solution
--
-- Use the UNION construct to query the two special cases
-- (all employees with no project assigned and
-- all projects with no employees assigned).
--
select e.name employee,p.name project
 from employee e, projalloc pa, project p
where e.emp_id = pa.emp_id
  and p.proj_id = pa.proj_id
union
select e.name,  null
 from employee e, projalloc pa
where e.emp_id = pa.emp_id(+)
  and pa.emp_id is null
union
select null,  p.name project
 from project p, projalloc pa
where p.proj_id = pa.proj_id(+)
and pa.proj_id is null
order by 1;

EMPLOYEE                       PROJECT
------------------------------ -----------
ALLEN                          PROJECT 01
BAKER                          PROJECT 01
BAKER                          PROJECT 02
FORD                           PROJECT 01
FORD                           PROJECT 03
FORD                           PROJECT 04
MILLER
SCOTT                          PROJECT 02
                               PROJECT 05
