Re: Please help with outer join....

 Try this ...

SELECT * FROM CUSTOMERS,FILES,FILEDETAILS,USERS  WHERE CUSTOMERS.CUSTOMERID = FILEDETAILS.CUSTOMERID  AND  FILEDETAILS.FILEID = FILES.FILEID  AND USERS.USERID = FILES.OUTBYUSERID (+)  AND  3 = FILES.FILEID; If results are not what your looking for, try this .....

SELECT * FROM CUSTOMERS,FILES,FILEDETAILS,USERS  WHERE CUSTOMERS.CUSTOMERID = FILEDETAILS.CUSTOMERID  AND  FILEDETAILS.FILEID = FILES.FILEID (+)  AND USERS.USERID = FILES.OUTBYUSERID (+)  AND  3 = FILES.FILEID (+); Hope this helps ...

sjudkins_at_christyind.com wrote in article <877556951.8323_at_dejanews.com>...
> I have the following situation...
>
> I have 4 tables that I want to do a "select" on:
>
> FILES:       FILEID,FILENAME,OUTBYUSERID,
> FILEDETAILS: FILEID,CUSTOMERID
> USERS:       USERID,USERNAME
> CUSTOMERS:   CUSTOMERID,CUSTOMERNAME
>
> OUTBYUSERID is a foreign key into the USER (USERID) table, but if
> no user has the file "checked-out", it is NULL.
>
> How should this select statement read? I know if OUTBYUSERID was
> always a foreign key into USERS that I could use:
>
> SELECT * FROM CUSTOMERS,FILES,FILEDETAILS,USERS
>    WHERE CUSTOMERS.CUSTOMERID = FILEDETAILS.CUSTOMERID AND
>          FILEDETAILS.FILEID = FILES.FIEID AND
>          FILES.OUTBYUSERID = USERS.USERID AND
>          FILES.FILEID = 3;
>
> And that does work as long as OUTBYUSERID isn't NULL, but when
 OUTBYUSERID
> is NULL, no matches are made.
>
> I guess what I am asking is what is the proper syntax for OUTER
> JOINS with multiple tables. If you could just change the above
> statement so that it works correctly...that would be OUTSTANDING! :)
>
> Thanks,
> Shayne
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
>
Received on Thu Oct 23 1997 - 00:00:00 CDT