Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Join on Joins in Oracle 8i
I have a total of 6 tables and want to join them all where relations
exist.
The first three store the actual names I want to pull. They start with table USERS that looks like ID | NAME and then I have LOCATIONS which is ID | NAME and then CLIENTS which is ID | NAME
Then the last three are my lookup tables which are MYEMPLOYEES which has EMPLOYEEID | USERID this relates a set of employees(employeeID) to a single manager(userid). Next is MYCLIENTS which has CLIENTID | USERID and this is used to related multiple clients to one user. Finally there is MYLOCATIONS which is LOCID | USERID and this is used to relate multiple locations to one user.
If I had a manager whose ID was equal to 1, I would want to pull out him/her with all of their clients, locations, and employees along with all of the employee's clients and locations. I do not need to distinguish between who is a manager and who is a employee in my returned recordset, so it could look like:
USERID | USERNAME | LOCATIONNAME | CLIENTNAME 1 | Aaron | Houston | Enron 2 | Bob | Dallas | Cyrix 3 | Alice | Austin | 4 | Richard | | Motorola
I tried this query but it errors out saying a table can only be outer joined once:
SELECT U.NAME AS USERNAME, L.NAME AS LOCATION, C.NAME AS CLIENT FROM (SELECT ID, NAME
FROM USERS WHERE ID IN (SELECT EMPLOYEEID FROM MYEMPLOYEES WHERE USERID = 1) OR ID = 1) U, LOCATIONS L, CLIENTS C, MYLOCATIONS ML, MYCLIENTS MC WHERE U.ID = ML.USERID(+) AND L.ID = ML.LOCID(+) AND U.ID = MC.USERID(+) AND C.ID = MC.CLIENTID(+)Received on Fri Feb 08 2002 - 13:15:53 CST