Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Join on Joins in Oracle 8i

Join on Joins in Oracle 8i

From: Aaron Rouse <aaron_rouse_at_yahoo.com>
Date: 8 Feb 2002 11:15:53 -0800
Message-ID: <a57b6daf.0202081115.305804ee@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US