Thanks for reminder to ensure that the data is in the PHONE table which didn't turned out to be the case. I am still debugging the JPA program to identify where the root cause is, and will try out the same query once the data is populated.
Joined: Oct 20, 2006
The original SQL now works after having corrected the JPA and populated PHONE table. I understand your concern wrt the mutable fields, they are only a hypothetical example thought of at the time. The actual table that I am working on are PROPERTY and PRICE with the following SQL:
However, I wonder whether it is possible to optimise it using various JOINS or subqueries? Btw, how would adding generated id field makes these records more immutable? I didn't know that it is possible to using a combination of composite key / IDClass together with a generated id, certainly not auto generated id?