Skip to main content

[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.namef.id FROM product p LEFT OUTER JOIN product_favorite f
ON (p.id = f.product_id AND f.user_id = :userId)

2. SELECT p.namef.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

Back to the top