There is a one-to-many relationship between the entities Student
and Presentation (a student can have many presentations).
Student S1 refers to Presentations P1 and P2
Student S2 refers to Presentations P3 and P4
What's the size of the collection returned by the following query:
select s from Student s inner join s.presentations p
The given answer is: 2
Enthu's explanation: Since there is an inner join between Student and Presentation, it just selects all the
Students for which there is a presentation. Since there are 2 such Students, it returns a collection of size 2.
But to my understanding an inner join forms the cartesian product of the involved tables. So the join
should yield the table
Then, if the Students are selected the result should be S1, S1, S2, S2. So the answer would be 4. I also tried
this scenario with glassfish/toplink and the result was indeed 4.
Enthuware is right - it will perform the inner join, and since the select clause returns only s, it will return only students that have at least one presentation, and it either returns the presentation collection or not (you don't tell us if it is lazy). If you wanted to return 4 results:
the query would have to be: select s, p from Student s inner join s.presentations p. Are you sure you checked the very same scenario on Glassfish?
thanks for your replies. I tried again, but I didn't get the desired result. My output always consists of 4 objects. The details are Tables:Query:
SQL generated by toplink: Query result: The result is the same whether table Presentation contains a foreign key constraint on Student.ID or not.
Has anybody an idea why it doesn't return just the 2 students ? Thanks.
I just ran the code and I am getting 2 rows. The code that I am running is given along with EJBPlus question bank (in Toy Application). The only difference that I see in the code that you've given here is that you are using private List<Presentation> presentations; while the code in the question (and in the sample program) is using private Collection<Presentation> presentations; But that shouldn't be an issue.
The sql that is being generated is:
SELECT DISTINCT t0.ID, t0.LASTNAME, t0.ROLLNUMBER, t0.FIRSTNAME, t0.SCORE FROM STUDENT t0, PRESENTATION t1 WHERE (t1.PRESENTER_ID = t0.ID)
Can you please tell me the version of the app server you are using?
Just realized that there is another difference in the query that you are using and the one given in question:
SELECT s from Student s INNER JOIN s.presentations p <-- INNER is missing in your query or is it just a typo here?
I tried again with "inner" but that had no effect (I think it's optional anyway).
I'm using Glassfish version 9.1_02 (build b04-fcs).
Maybe it's an versional problem, but I don't really belive that. Because joins will surely be
frequently used in practise and I guess the problem would have been found / fixed years ago.
But who knows.
Anyway, thanks to showing me your generated SQL, I know how it should work. That's sufficient.
Thank you very much.
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com