Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » jpql query mystery
jpql query mystery [message #535381] Sat, 22 May 2010 22:53 Go to next message
Olu  is currently offline 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 Go to previous messageGo to next message
James Sutherland is currently offline 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
Re: jpql query mystery [message #538174 is a reply to message #535381] Sun, 06 June 2010 02:34 Go to previous message
Olu  is currently offline Olu
Messages: 29
Registered: October 2009
Junior Member
thanks
Previous Topic:Map to a column that may not exist
Next Topic:unable to get lazy loading to work
Goto Forum:
  


Current Time: Sat Oct 25 16:58:49 GMT 2014

Powered by FUDForum. Page generated in 0.02673 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software