| Query with multiple matches in collection [message #558450] |
Sun, 12 September 2010 23:47  |
 Miguel Missing name
Messages: 9
Registered: September 2010 |
Junior Member |
|
|
I have two tables, table1 has a one-to-many relationship to table2. The entity for table1 has a collection attribute with all related records in table2. How can I retrieve records in table1 that have multiple record matches in table2, for different combination of values? For example, I want all records in table 1 for which there are three different records in table2 with the field DESCRIPTION in table2 being either "BLACK", "WHITE", or "BLUE". All three colors must exist among the related records.
[Updated on: Mon, 13 September 2010 09:49] Report message to a moderator |
|
|
|
| Re: Query with multiple matches in collection [message #558783 is a reply to message #558637] |
Tue, 14 September 2010 12:32  |
Miguel Missing name
Messages: 9
Registered: September 2010 |
Junior Member |
|
|
Thanks, I ended up writing a native query to get out of the bind, but I will replace the code after I test your JPQL solution. I ended up doing a select subquery In native SQL language to retrieve the ids of the rows that had 3 matching records on the joined tables, then retrieving the entities using the id list. I will compare the performance once I write the JPQL method,.
The actual scenario is of course more complicated, with the criteria for each match in the joined tables involving multiple expressions for different columns in the tables. I had wondered whether I could implement a query by example using three different example objects for each criteria. That would greatly simplify the JPQL coding. Building expressions with the expression builder is not for the faint of heart.
[Updated on: Tue, 14 September 2010 12:44] Report message to a moderator |
|
|
Powered by
FUDForum. Page generated in 0.03307 seconds
] 
Search
Help
Register
Login
Home
