Issue
I am trying to convert this SQL into a JPA @Query call, and am struggling to understand how to handle multiple table calls in one JPA Query, specifically i am trying to return a Count of all the Users that are assigned to a Program.
The DB structure is shown:
The query I want to translate is:
SELECT *FROM User WHERE id in ( SELECT user FROM UserProjectAssignment WHERE project in ( SELECT id FROM Project WHERE program = programId))
My current Query is:
@Query("SELECT COUNT(u) FROM User u, UserProjectAssignment upa, Project p WHERE u.id = upa.project AND WHERE upa.project = (p.program = :programId)")
Long countUsersAssignedToProgram(@Param("programId") Long programId);
Solution
Basically what you need here is query with JOIN s But one can form the query only if he knows the class structures.
You can change the mysql query like this
Select user from User user join UserProjectAssignment upa on user.id = upa.userid join Project p on upa.projectId = p.id join program pgm on p.programId = pgm.id ;
Answered By - Ashiq K