Eclipse Community Forums: EclipseLink

Help

Home

Home » Eclipse Projects » EclipseLink » jpql query mystery

Show: Today's Messages :: Show Polls :: Message Navigator

jpql query mystery [message #535381]

Sat, 22 May 2010 22:53

Olu

Messages: 29
Registered: October 2009

Junior Member

I have a fairly simple JPQL query as below:-

 select m from MailingList m where m.institution='XXX' and (m.applicationAppId.invoiceId IS null or m.applicationAppId IS null)

A MailingList object may or may not have an Applications object.

An Applications object may or may not have an Invoices object.

I simply want the statement to return all the MailingList objects that match the institution name (in the full query there are a few other fields here too) and either:
1) do not have an Invoices object OR
2) do not have an applications object

However, the above statement only returns MailingList objects that have an Applications object (applicationAppId) but don't have an invoice object even if there are MailingList objects.

Furthermore the query:-

select m from MailingList m where m.institution='Park View Community School' and m.applicationAppId is null

Works as expected and the query:-

select m from MailingList m where m.institution='Park View Community School' and m.applicationAppId.invoiceId is null

also works as expected.

However ORing the two last parts of the query together doesn't return both of the above.

and (m.applicationAppId.invoiceId IS null or m.applicationAppId IS null)

Seems to me that m.applicationAppId.invoiceId seems to filter out objects that do not have an Applications object and m.applicationAppId IS null is effectively ignored

Can anyone shed any light or suggest a way I can achieve my desired outcome?

thanks,

[Updated on: Sat, 22 May 2010 23:10]

Report message to a moderator

Re: jpql query mystery [message #536939 is a reply to message #535381]

Mon, 31 May 2010 15:03

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

This has to do with joins in databases. You must use an outer join for this.

i.e.
select m from MailingList m left outer join m.applicationAppId a where m.institution='XXX' and (a.invoiceId IS null or m.applicationAppId IS null)

James : Wiki : Book : Blog : Twitter

Report message to a moderator