I have two tables named Student, and Games. Student Id is the foreign key in the Games table, each student can opt for multiple games(OneToMany relation).

Here I need a JPQL query to fetch students' lists and the games list. Condition is: when we try to search for students who are playing Cricket the returned list should contain the list of all students who play cricket and their opted games(All games including cricket).

Example: Student-1: Football, Cricket Student-2: Hockey, Cricket Student-3: Volleyball, Football Student-4: Cricket

Expected Output: Student-1: Football, Cricket Student-2: Hockey, Cricket Student-4: Cricket

CodePudding user response：

I found the solution myself added below condition in where clause

select s from studentsEntity s left join fetch gamesEntity g
where s.studentId in (select s1.studentId from studentsEntity s1 left join gamesEntity g1 where s.studentId=s1.studentId and g1.name='Cricket')