-- ############################################################################################# -- -- %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