db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Øystein Grøvlen <Oystein.Grov...@Sun.COM>
Subject Re: [DERBY-39] An ON clause associated with a JOIN operator is not valid
Date Tue, 14 Feb 2006 16:43:03 GMT
Erik.Bengtson@intl.westernunion.com 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

Mime
View raw message