[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
Re: [eclipselink-users] Conditional LEFT JOIN in JPQL
|
Hi, Tom,
2010/5/5 Tom Ware
<tom.ware@xxxxxxxxxx>
I appologize for my lack of understanding but...
How will the results of the following queries be different (assuming a mapped relationship between dealer and vehicle using v.dealer_id = d.dealer_id as the foreign key relationship)?
1. 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
2. SELECT d.name, count(v.id) FROM dealer d LEFT OUTER JOIN d.vehicles v where v.type = 'New' or v.type isnull GROUP BY d.name
Second query will be missing dealers, who only have old vehicles.
Taking other Bernards example:
1.
SELECT p.name, f.id FROM product p LEFT OUTER JOIN product_favorite f
ON (p.id = f.product_id AND f.user_id = :userId)
2. SELECT p.name, f.id FROM product p LEFT OUTER JOIN p.product_favorite f
WHERE f.user_id = :userId OR f.user_id isnull
Second query will be missing products, which are favorites only to other users.
And my case:
SELECT
c.name, e1.fieldValue AS language, e2.fieldValue AS nationality
FROM Country c
LEFT JOIN ExtCharFields e1 ON
c.id=e1.country_id AND e1.fieldName = "language"
LEFT JOIN ExtCharFields e2 ON
c.id=e2.country_id AND e2.fieldName = "nationality"
It isn't possible to write JPQL query at all
--
Sincerely,
Vidas