Hi sql Gurus, I have three tables. Briefly, lets call them T, O and P. Related thusly:
T.objectid = O.objectid; O.patientid = P.patientid I am trying to return data from P and a derived table consisting of T and O like so: SELECT P.patientid, P.lastname, P.firstname, DT.datestamp FROM patientcore P, (select O.patientid, O.datestamp, T.dos from objects O, tbl_dailylog T where T.dos BETWEEN '9/20/2004' AND '9/21/2004' and O.objectid=T.objectid) DT WHERE p.patientid=dt.patientid The above query works as expected. However what I would really like to do is replace "P.patientid=DT.patientid" with "p.patientid*=DT.patientid". But when I do that I get the following: Server: Msg 303, Level 16, State 1, Line 1 The table 'objects' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause. So... what do I need to do to make this work? Ultimately I would like to return columns from O. My interim workaround is to squash T from the derived table and query O.datestamp instead of T.dos. Thanks in advance, Alex ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184948 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
