multiple outer joins - how does that work ?

I'm trying to create a table from 5 other tables using outer joins, but I get
ORA-01417: a table may be outer joined to at most one other table

How can I get round this ? Here's the basis for the table creation -

SELECT COUNT(*) FROM
PILOT.PERSON P
,PILOT.MEMBERSHIP M
,PILOT.HOUSEHOLD H
,PILOT.COMMUNICATION C
,PILOT.BASE G
WHERE P.PRN(+) = M.PRN
AND H.HRN(+) = M.HRN
AND C.MEMBERSHIP_NUMBER(+) = M.MEMBERSHIP_NUMBER
AND G.POSTCODE = H.POSTCODE(+)

Any input would be greatly appreciated.

Specify only one outer join (+) to this table, then retry the
operation.

"In a query that performs outer joins of more than two pairs of tables, a single table can be the NULL-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C."

Use subqueries or use uninon fn...

Quote:

---

"In a query that performs outer joins of more than two pairs of tables, a single table can be the NULL-generated table for only one other table....

---

In certain situations you have to use union and subquerys instead outer-joins because using them in the way that you need you break the rule described above in the quote..

I don't know the logic of your table's structure , so maybe this query don't give the results that you expect. But you will note get an ORA-01417 error.

SELECT COUNT(*) FROM
PILOT.PERSON P
,PILOT.MEMBERSHIP M
,PILOT.HOUSEHOLD H
,PILOT.COMMUNICATION C
,PILOT.BASE G
WHERE M.PRN(+) = P.PRN
AND M.HRN(+) = H.HRN
AND M.MEMBERSHIP_NUMBER(+) = C.MEMBERSHIP_NUMBER
AND H.POSTCODE (+) = G.POSTCODE

ccastaneda :

the suggested query will not give result wat he wanted....if number of records dont match in the tables M,P,H,C.....

Abhay.

Quote:

---

Originally posted by ccastaneda
.. maybe this query don't give the results that you expect. But you will note get an ORA-01417 error.

---

The point in my reply was to give him a query that don't get an ORA-1417 error. If the query don't give the results taht he expect the next step is use unions and subquerys.

The only person who knows if this query give him the results that he expect is Horace. He knows his tables, the cardinalities of the relationships between the tables, the reason of the query itself, and all that stuff... deduce all that info from the joins of the query could not be accurated...