Actually, I found a solution to this via the following query. How
could I define the common query(used twice) that is in the IN
statements as a variable so that it(the query) does not need to be
typed out more than once and the variable could just be plugged into
those?
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,
(SELECT ML.LOCID, L.NAME, ML.USERID
FROM LOCATIONS L, MYLOCATIONS ML
WHERE L.ID = ML.LOCID
AND ML.USERID IN (SELECT ID
FROM USERS
WHERE ID IN (SELECT EMPLOYEEID FROM MYEMPLOYEES WHERE USERID
= 1)
OR ID = 1)
) L,
(SELECT MC.CLIENTID, C.NAME, MC.USERID
FROM CLIENTS C, MYCLIENTS MC
WHERE C.ID = MC.CLIENTID
AND MC.USERID IN (SELECT ID
FROM USERS
WHERE ID IN (SELECT EMPLOYEEID FROM MYEMPLOYEES WHERE USERID
= 1)
OR ID = 1)
) C
WHERE U.ID = L.USERID(+)
AND U.ID = C.USERID(+)
Received on Fri Feb 08 2002 - 15:40:30 CST
