Hi James
You are correct that adding WHERE v.type IS NULL OR v.type = 'New' will
bring back a list of dealers with either no vehicles or a new vehicle.
However this has the effect of excluding dealers who only have old vehicles.
I suspect this is a deficiency in the actual JPQL specification. No matter
what combinations of IS NULL and IS EMPTY I use, it always generates a WHERE
clause instead of an ON clause for the "v.type = 'New'" check, which
immediately makes my SQL incorrect.
Thanks
Gary
On 9/12/07, James Sutherland <jamesssss_at_yahoo.com> wrote:
>
>
> What you are basically querying for is all Dealers who have either no
> Vehicles or a "new" Vehicle.
> You should be able to use an OR for this,
> i.e.
> WHERE (v.type = null) OR (v.type = 'New')
>
> But IS NULL and IS EMPTY should work as well, what was the JPQL you tried
> and the SQL it produced?
> Also what database are you using?
>
> ---
> http://wiki.java.net/bin/view/People/JamesSutherland James Sutherland
>
>
> Gary Jacobson wrote:
> >
> > Is there an equivalent to the SQL ON clause in JPQL?
> >
> > Let's say you want a list of car dealers and how many new vehicles
> they're
> > selling.
> >
> > In SQL you can say:
> >
> > SELECT d.name, count( v.id )
> > FROM dealer d LEFT OUTER JOIN vehicle v
> > ON v.dealer_id = d.dealer_id AND v.type = 'New'
> > GROUP BY d.name
> >
> > In JPQL, you only have the WHERE clause which limits your results,
> making
> > the outer join useless:
> >
> > SELECT d.name, count( v.id )
> > FROM dealer d LEFT OUTER JOIN d.vehicleList v
> > WHERE v.type = 'New'
> > GROUP BY d.name
> >
> > (My actual query is a fair amount more complicated, and I've tried
> messing
> > around with IS NULL and IS EMPTY to no avail)
> >
> > Is there any way to do this?
> >
> > Thanks
> > Gary
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/LEFT-OUTER-JOIN---ON-clause-equivalent--tf4430420.html#a12641034
> Sent from the java.net - glassfish persistence mailing list archive at
> Nabble.com.
>