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 |
|
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
|
|
|
|
Powered by
FUDForum. Page generated in 0.03331 seconds