[EMAIL PROTECTED] wrote:
Hi,
This is issue DERBY-39, can someone give an opinion on this?
It works in MSSQL and DB2
SELECT UNBOUND_P.PROJID FROM applicationidentity0.PERSONS THIS,
applicationidentity0.PROJECTS UNBOUND_P WHERE
EXISTS (SELECT 1 FROM applicationidentity0.PROJECT_MEMBER THIS_PROJECTS_P
LEFT OUTER JOIN applicationidentity0.PROJECTS UNBOUND_P_NAME ON
UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
WHERE THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND
THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND UNBOUND_P.PROJID =
THIS_PROJECTS_P.PROJID AND UNBOUND_P_NAME."NAME" = ?
AND (THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ? OR
THIS.DISCRIMINATOR = ?))
I'll admit outer joins are not my area of expertise, but could you
explain what you are trying to achieve here? You are doing a left outer
join on two tables and specifies an ON-clause that does only refer to
one of these tables. So what makes this an outer join? Is seems to me
to be a cartesian product. Why not just move the predicate in the
ON-clause to the WHERE-clause? Or am I missing something?
--
Øystein