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 19:10] by Moderator
Home
