Re: Join on Joins in Oracle 8i

So LOCATIONS is N:M related to USERS via the relation table MYLOCATIONS, and CLIENTS is N:M related to USERS via MYCLIENTS? If you want the clients and the locations of a user via outer joins you should specify (using the aliases from your query):

WHERE U.ID = ML.USERID(+)
AND	  L.ID (+) = ML.LOCID
AND	  U.ID = MC.USERID (+)
AND	  C.ID (+) = MC.CLIENTID

Jaap.

On 8 Feb 2002 11:15:53 -0800, aaron_rouse_at_yahoo.com (Aaron Rouse) wrote:

>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 Sun Feb 10 2002 - 04:30:10 CST