Hi, You might want to try: Select p.firstname, Case v.type WHEN 'v1' THEN v.type WHEN 'v2' THEN 'null' ELSE 'null' END " + from Patient p left join p.vaccinCollection v
However, this approach still requires post-processing of 'null' string to null value. Fay ----- Original Message ---- From: Michel Ganguin <mic...@ganguin.net> To: users@openjpa.apache.org Sent: Mon, October 26, 2009 10:46:22 AM Subject: left join with filter on joined table Hi, I have two tables: patient ------- id firstname lastname vaccine ------- id patientid date type With an ejbqlquery i want to retrieve all patients that have 0 to n vaccines Select p.firstname, v.type from Patient p left join p.vaccinCollection v result: p1, v1 p1, v2 p2, v2 p3, null Now I want to filter by vaccine type (all patients that have 0 to n vaccines of type v2): Select p.firstname, v.type from Patient p left join p.vaccinCollection v where v.type = 'v2' result: p1, v2 p2, v2 But this filtered out patients without vaccine, so i tried: Select p.firstname, v.type from Patient p left join p.vaccinCollection v where v.type = 'v2' OR v.type is null result: p1, v2 p2, v2 p3, null This looks like what I want but doesn't work for v1 Select p.firstname, v.type from Patient p left join p.vaccinCollection v where v.type = 'v1' OR v.type is null result: p1, v1 p3, null I want to have "p2, null" in the result. How can I do that? I'm not against getting all result (first query) and filter out the unwanted data but this vaccine table becomes very huge and it sould be a dramatic performance loss! Is there a way to avoid post-join-filtering by adding a query hint or something like this. Or to use parameters in entity relations? If there is no solution I would use native queries but how can I map join queries to entities? Thanks in advance. Michel Ganguin