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.